set affinity for SqlExpress: size

Showing posts with label size. Show all posts

Friday, March 30, 2012

how to shrink Trx Log

Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details at http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.T
K2MSFTNGP03.phx.gbl...
> You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions
> out to a backup device, then you should be able to shrink the transaction
log. Then set up a
> maintence plan to perform periodic transaction log backups to control the
growth of the log file
> in the future.
> David

how to shrink Trx Log

Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
>> Shrink Log file does not seems to make the file size any smaller.
>> The file size remains the even after shrinking it a few time.
>> The GUI seems ez to use but I may be doing it wrongly.
>> How do I shink the Log file?
> You can't shrink it if it's full of transactions. Perform a log backup to copy the transactions
> out to a backup device, then you should be able to shrink the transaction log. Then set up a
> maintence plan to perform periodic transaction log backups to control the growth of the log file
> in the future.
> David

how to shrink the size of tempdb other than reboot the server

Hi,
I work with sql server 2000.
Wondering what's the best practice to shrink the size of
tempdb other than reboot the server. I don't want to
interrupt productions db running on the server.
JJtry dbcc shrinkdatabase, dbcc shrinkfile
Richard
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:019401c39283$bc0aeeb0$a401280a@.phx.gbl...
> Hi,
> I work with sql server 2000.
> Wondering what's the best practice to shrink the size of
> tempdb other than reboot the server. I don't want to
> interrupt productions db running on the server.
> JJ|||Howdy
You can reduce the tempdb size, BUT, if the tempdb was created with
( say ) 1000 MB initial size, 1000 MB is the smallest you can
shrink it to.
Cheers
SG
Posted via http://dbforums.com

how to shrink the logfile

How to Shrink SQL Server database

First of all thanks for taking interest in this thread.

I have database in Microsoft SQL Server and its size is appx. 427 GB. It consists of data from 2006. Everyday I expire data of 2-3 days and then try to shrink database as much as possible.

But it frees only 2-3 MB after 7-8 hours process. And in that database there some files which do not shrink even I put one of them on shrinking for whole day.

And as per company policy, I am only allowed to expire data which are 15 months older.

I am facing very much problem as my daily import and publish activity some times got failed for that reason.

If anyone knows the solution please tell me the solution.The simple answer would be don't shrink it. Shrinking db files is really not a good idea especially on a regular basis.

Moving thread to proper forum.|||Then what should I do?|||Then what should I do?Don't shrink the file :)

Are there any pressing needs for why you are shrinking? Is this an active database with ongoing modifications?|||Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.

I have 9 drives of 100 GB and there is restriction that each drive must have 10% free space. As daily input to new database done, it grows rapidly but older one doesn't frees occupied space even after expiring data older then 15 months.

Thus, my application which does daily import and publish activity get failed.

That's why I shrink the database.

Please tell me the solution for that.|||Buy more disks. I am serious - why fit your database to your server not the other way round?
10% free space is rather low too. You can't even do a proper physical defrag AFAIK.

Check out the BoL entry for shrinking databases and files. Paul Randal (SQL Server enginge lead at MS) ensured that some of the disadvantages of this approach made it into the manual. If you try googling you will find hundreds of examples of people wnating to do what you want to do and being advised not to do it.|||Thanks for serious advice! But this is not my pc or my company's pc but it is my company's clients server pc.

And in near future they have no plan to add any drive as there is much total free space. But problem is that on some drive it has 50 GB free space and on some drives only 12 GB.

I also tried to move datafiles but some files are more than 50 GB so cant move to other drive.

So still any serious advice?|||Listen to the Flump. The Flump speaks Wisdom.

Do.
Not.
Shrink.
Database.

I wish Microsoft had not even included database shrinking as an option.

Tell.
Your.
Client.
To.
Buy.
More.
Disk.
Space.|||I *love* useless advice.

Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.

I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.

Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.|||I *love* useless advice.

Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.

I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.

Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.

It's NOT useless advice. It is sound, practical advice.

The only thing I would add to it would go something like this:

What you face is not a technical issue. It is a business issue. Business people respond better to technical problems when they are presented in business terms. This usually means presenting an issue and couching it in terms of cost versus benefit.

I would approach the problem in this fashion:

John [or my manager's name],

I am technically unable to shrink the database because of limitations in MS SQL Server. There are several ways to deal with this issue:

1. We can do nothing. Eventually the application will fail because there is insufficient storage. This will cost the business no money, but the downside is that the application will no longer function and users will not have access to the most recent data.

2. We can add disk space to the existing server. This would involve purchasing XX disks at YY cost per disk for a total of ZZZ dollars. I do/do not have experience installing these disks and so there is/is not additional cost associated with bringing in a technician to do the installation.

3. We can selectively eliminate more data than what is being purged now. Instead of C years' retention, we can instead choose B years' retention. This will/will not put us in violation of selected audit rules/business requirements. Once more data has been eliminated, it may be possible for me to shrink the database effectively.

My recommendation would be to implement Option X above.

I look forward to working through this issue with you.

Regards,

Don B. Azalea

My advice:
1. Never approach your manager with a business problem without having at least two solutions that you have thought through.

2. Always include the option to do nothing. It at least rephrases the problem in terms that a manager can comprehend.

3. Learn to speak in terms of Return on Investment. It will make you a more well-rounded (and hence valuable) person in your operation.

4. Never talk down to your boss/supervisor. They make decisions that affect your paycheck. Speak to them in terms that they can understand, but never assume that they are ignorant. Try to step out of your world on occasion and see things from their point of view. It can be very enlightening.

Good luck.

By the way, I agree with Pootle and Blindman here: shrinking the db is NOT the way to go.

Regards,

hmscott

Edited for clarity.|||Pootle Flump...nice sig!!!

:D

hmscott|||Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.Hint: It's not the advice that is useless...|||sometimes, late at night, when the wind is still and the water on the pond is a smooth black mirror, you can hear the Flump calling out, wailing a sad song of how you shouldn't shrink databases.

who will listen?|||If the advice doesn't solve the problem, then it's useless-IMO. The problem isn't whether or not to shrink the database-that's already been decided (and not at my level). If I disagree with that decision I can quit or be fired.

The problem is *how* to shrink the database. Your advice doesn't solve that, so it's useless. JMHO.|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)|||part of my job is to provide managers, users and developers with what i consider the best practices and the best options when it comes to all matters database. if i do not do that, then I am failing them by not providing them the best solution possible.

i understand that money might not always be there for the hardware I desire or require, but it is my job to point out the need. from what i understand you can mitigate the effects of the shrink with a little index defrag or or reindex. however it seems part of the problem is the time the shrink takes and the process will be even longer if you add this step in.

so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.|||so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.

:D

I have never done such a thing. never

Well, maybe once...there was a time when I dropped the entire schema for our data warehouse...but that's a tale for a different day.

Regards,

hmscott|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)

Don't know what your situation is, so I can't address it directly.

Conditions under which I can understand and recommend that a database be shrunk:

1. You've purged a "significant" amount of data from the data files (through truncate or delete or drop). You do not expect to re-add this data anytime soon.

NB: "Significant" is subjective here, it can be any value, but I would generally look for anything greater than 25%.

2. You've had an issue with a log file filling up and extending beyond your intended size limit.

Some ways of shrinking your database...
Instead of using DBCC SHRINK DATABASE, consider using DBCC SHRINKFILE. This can be less expensive in terms of I/O on the disk.

You might also consider deleting some indexes in an attempt to trade off performance for space.

You might consider moving some data to another server, making a linked server and accessing the moved data through a linked server query.

On a side note, if you think that you might lose your job on account of this one issue, then your job is already lost. I do not mean this to be facetious or rude. You must have sufficient confidence in your skills and trust in your boss to be able to admit to ignorance of a particular issue and be able to get the help you need to fix it. If that confidence isn't there or the trust isn't there, then it's only a matter of time before your boss will find someone who has the skills or in whom he can trust.

Just my .02. It's worth what you paid for it.

Regards,

hmscott|||Well, I agree with your comments about the job. Long term either I'll be gone or my boss will, but that doesn't mean I want to (actually, can afford to) leave right now.

I appreciate your advice & will look into SHRINKFILE. Somehow overlooked that while I was checking on SHRINK DATABASE.

Thrasy, essentially those options were presented to my manager-who responded that buying more disks was out. He knows the database can be shrunk but it's up to me to actually do it and all the advice I've found so far is "don't do it". Even though I agree with it, it's still useless because I can't follow it & still keep my job.|||No problem. FWIW, it appears that I will soon be moving on though not by my choice nor by my manager's choice.

Long story, but it sucks 'cause I like it here.

Regards,

hmscott|||Calvin, obviously your boss does not respect your opinion or advice.
And after reading your posts in this thread, equally obvious to me is WHY he doesn't respect your advice.
Pootle gave good advice. The fact that your boss refuses to listen to the advice of some of the best DBAs in the world, coupled with your labeling that advice as "useless", makes me think that you must be a pretty good fit for your current position of employment. I can see why you are so keen on keeping it.
Basically, if your boss refuses to listen to you that is not Pootle's problem, and it is not my problem, and it is not SQL Server's problem. Who's left?|||Guys! Guys! Guys!

Lots of reply, thanks for that especially Calvin-c, flump and hmscott.

I got confused after reading all replies.

My actual problem is:

-> How to more effectively shrink SQL database?
-> Is there any alternate way through which I can get free spaces after expiring?

As I told there is appx. 270 GB free space available but in scattered form means in one drive there is available free space is 40 GB while in other 12 GB. And sizes of datafiles are very big so I can't move them from one drive to another.

So I don't think that I have any option rather that going for shrinking database however it is not preferable.

And as there is lots of free space, so I can't demand for more disk space in any condition.

So still any serious advice?|||Do you know how much data you actually delete? You know it is more than a few MB yes? Also what commands are you executing?

Just in case I forget to mention later - you need to reindex after a shrink too as you will have fragmented your indexes and the database will run poorly for many reporting applications.|||Also - what version? Is this 2000 or a database that has been upgraded to 2005? If so please run DBCC UPDATEUSAGE|||I use SQL 2000. And everyday first I expire data oldest 2-3 days data using min (timestamp) query.

Appx. 3 GB of data per day, however it depends on the log file for that day but on the average it shows reduced size of that database up to 6 to 9 GB.

In expiring, I dont face any problem but after then when I shrink database. There are total 36 tables and 24 tables get shrunken successfully but 12 tables do not shrink and those tables use much of the memory. And at the end it frees hardly 3-4 MB.

If you know how to shrink those 12 tables then most probably my problem will be solved.|||What code are you running to get the used and available space for your database? And what code do you run to shrink it?|||I recently had to shrink a 50 gig database after major clean up (previous company was ...). It went from 50 to 1 when I was done, but I had to re-run the shrink probably 100 times over a weekend before it was done. Also, I was able to do it before going live with the db.

I also would switch back and forth between shrinkfile and shrinkdatabase. Sometimes it would free up nothing, and then every 10 times or so it would give me back a couple gigs.|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.

And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.

I have also tried shrinkdatabase and shrinktable but they dont work.|||This is what I use but I only need to deal with 2 GB database since I have my clients running MSDE Rel A. I don't know how long it will take on a 250 GB database.

HTH

backup log MyDataBase with truncate_only
dbcc loginfo
go

dbcc shrinkfile (MyDataBase_log);
dbcc loginfo
go

dbcc shrinkfile (MyDataBase_DATA);
dbcc loginfo
go|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.
Presumably that just deletes data from tables?

And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.

I have also tried shrinkdatabase and shrinktable but they dont work.
Well - you don't shrink tables you shrink files\ databases.

Did you run updateusage?

What do you get if you run this:
USE my_db
go

CREATE TABLE #helpfile
(
name sysname
, fileid smallint
, filename nvarchar(256)
, filegroup sysname NULL
, size nvarchar(18)
, maxsize nvarchar(18)
, growth nvarchar(18)
, usage varchar(9)
)

INSERT INTO #helpfile
EXEC dbo.sp_helpfile

SELECT *
, file_size_mb - space_used_mb AS spare_space
FROM (
SELECT name
, usage
, CAST(SUBSTRING(size, 1, CHARINDEX(' ', size) - 1) AS INT) / 1024 AS file_size_mb
, CAST(fileproperty(name,'SpaceUsed') * (8/ 1024.0) AS INT) AS space_used_mb
FROM #helpfile
) AS der_t

DROP TABLE #helpfile|||It shows all datafiles and spare space. The total space is 24813 MB. And there are 4 files which have spare space more than 5000 MB. So if I manage with there files, my problem will automatically be solved.

But main problem is that as I earlier said; when I shrink these files it hardly frees 1-2 MB after whole day process.|||Can you confirm you ran updateusage before running that script?|||What is updatemanage? I don't know what it is? Will you please tell me what it is and how will it be useful to me?|||Sorry, I mean updateusage. Will you please tell me what it is?|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.

USE my_db
GO

DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.

USE my_db
GO

DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||I executed both codes but no change in result. It shows the same as before.|||I executed both codes but no change in result. It shows the same as before.Good stuff.

I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.

USE my_db
GO

SELECT name,size
FROM sysfiles
GO

DBCC shrinkfile (file_name, 0)
GO

SELECT name,size
FROM sysfiles
GO

Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?|||I find this threaad amusing

What do you mean you "expire" data?

Do mean you delete data?

Then if that's what you are doing, then why do you keep around the other database that is "no longer in use"

And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.

The reason is, that space is allocated, and when you delete the data, the space becomes available, and you don't incur the overhead of shrinking, AND growth.

And how come nobody asked about the transaction log size?|||I *love* useless advice.

Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.

This is priceless

Would you like fries with that advice?|||And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.You are kidding right? The first two pages pretty well talk about nothing else.|||You are kidding right? The first two pages pretty well talk about nothing else.

Where?

I just reread it. Nowhere does anyone say that it's just gonna grow again anyway, so why incurr the overhead?

Want tto post a quote where it discusses that?|||if you can't shrink the data, and...
you can't add new disk space, and...
you cannot persuade people of another course of action, then...

basically you are stuffed with a capital F

then Id examine the data that you are archiving... does the full 427Mb db need to be where it is, does it really need to be so close to production (ie could it be archived off in a better mechanism). how often do they access data that is 15 months old., can it be filleted to remove much of the redundancy, or just to keep the data that is actually required

id want to seriously look at table design.. are there columns that are poorly defined in terms of size, are there redundant columns/tables that can go
are there tables or rows that can be dropped within the archive.

but that is not a short term fix, and right now I guess short term fix is what is required. The most immediate short term fix is bigger disk.|||This is a common myth, that you can shrink databases without removing data. It is NOT a zip file, it does not compress.

No amount of sitting on the suitcase will allow it to buckle any easier.

What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size.

You can site this article from Microsoft explaining what "shrinking" the database actually does: DBCC SHRINKFILE (http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx)

I am going to assume that you ARE backing up the database and dumping the transaction logs when you do so.

Bits of data are not compressible. You can reorganize it, and label it in a way that allows it to be more streamlined and smaller, but those are programming/coding changes to the database itself. You can do performance tuning on your database, and implement some best practices if possible, but you still can not physically reduce the size of a bit of data.

If the requirement is that you MUST keep 15 months of data, then you must have enough space to store 15 months of data. If your company has grown in any way since the inception of the database, then your database will also grow accordingly.

That's what we've been trying to explain. Shrink is a misnomer. Think of it as a full glass of water in a plastic cup. You can put your hand over the top and suck all the air out of the little space above the water. But it does not reduce the water, and as soon as your hand (the 10% growfile function) comes off the top of the glass, you still have a container that is the same size and still has the same amount of water.|||Stop that, Mo.

Damn.

You're supposed to say "Refer to the BOL for details of the SHRINKFILE process". ;)

And you forget that this thread is being posted to by primarily men. Your analogy should refer to a glass of beer to be most effective.

Other than that, good description!|||Only rednecks serve beer in plastic cups! =P|||What? Glasswear at a kegger? That's just askin' for cracked skulls later on in the evening.

Besides, we like to drink our beer from a mason jar.|||fyi, here's a script courtesy of MVJ over on sqlteam that might be of use to you:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355|||Want tto post a quote where it discusses that?Sure:
http://www.dbforums.com/showpost.php?p=6283790&postcount=8489 ;)

We pointed out several reasons why he should avoid doing this if at all possible however we didn't make your point because of this little nugget:
Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.|||Liebling - I've read the BoL 2005 entry (which has more detail than B0L 2000 on this) and Inside SQL Server 2005 and that is not my understanding of shrinkfile.|||I haven't read the 2005 entry, but my assumption was that if they were not willing to spring for hard drive space (really cheap) then they were probably also still on 2000. In fact, I think he stated he was on SQL 2000 (http://www.dbforums.com/showpost.php?p=6282845&postcount=25). You can't refer to SQL 2005 BOL's because they aren't the same beast. Similar, but a lot of recoding and changes went into 2005.

In my experience, and according to every SQL 2000 reference to SHRINKFILE I've read thus far, that is exactly what it is doing.|||Ok - reread the 2000 entry. It is, as far as I can tell, the same as the 2005 entry except the 2005 entry is more thorough.

What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size. I don't get that at all - my understanding and what it says in BoL is that pages are moved from the end of the file and allocated to the beginning of the file (depending on various stuff, including the amount of free space indicated when running the dbcc command, size of model and min size of the db). There is no mention of moving stuff from one file to another except when emptyfile is specified.

Ultimately the mechanism behind shrinkfile is (as I see it) something like indexdefrag. It simply moves pages within the file around, just for a different purpose and using a different algorithm. Nothing within pages changes so the total used space remains constant.|||Right, it moves the pages that are not complete around a bit. Most of those pages are actually placeholders created by the grow function from my understanding. But yes, much like defrag it still doesn't really change the size of the container just the places the data is stored. It never shrinks the data.

It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.|||Right, it moves the pages that are not complete around a bit. It moves any pages that fall in the part of the file to be "emptied" around irrespective of their contents. It just sees them as 8kb blocks. (TMK of course).

It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.Way ahead of you :) Wrote the below this lunch break.

SET NOCOUNT ON
GO
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY SIMPLE
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO
----
--take 2
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY FULL
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO Produces (depends on your model settings of course):

order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.27342
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98162
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.15759
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248
order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.28123
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98944
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.16540
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.

I'm betting it's a browser issue. I use firefox and I've never been able to preserve the colorization of code either. I assume that's what you are referring to? probably it only works with IE.|||I am an IEer. Works fine on SQLTeam not here. And it was the same at my old job and now this one, both QA and SSMS. Poor Poots :o|||Good stuff.

I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.

USE my_db
GO

SELECT name,size
FROM sysfiles
GO

DBCC shrinkfile (file_name, 0)
GO

SELECT name,size
FROM sysfiles
GO

Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?

I use above code for 2 days and for around 20 hrs. but it hardly frees 4-5 MBs. So it is also not working properly.

And this database is not used for writing only used for report viewing. As having less memory, we dont hv backup of this database.
No, I dont run dbreindex or indexdefrag as it is for faster retrival of data but we dont face any problem of retrival.|||What about back ups?|||What about back ups?

As I told in post no.58 that we dont hv backup as having less free space.|||As I told in post no.58 that we dont hv backup as having less free space.

Well, that has got to be as good a reason not to buy more disk space....

If you go to the Access Forum, I'm sure they'll have an answer for you|||Access forum? But I am using SQL. then how can they know my problem?|||Access forum? But I am using SQL. then how can they know my problem?Don't even try to make sense out of Brett's coment... It makes sense in a larger context (based on many different threads), but its just outright bizzare in the context of just this one thread.

Brett's having one of those mornings... Or maybe having three or four of those mornings, all in one day!

-PatP|||1. You don't have any backups, and it sounds like a VLDB...how quaint
2. You don't try the code in the link from Kristen...how clever
3. You ignore all the advice from everyone here in the SQL Server forum

I just thought maybe all of the sophisticated programmers in the Access forum might have the answer you are looking for

Nomad|||you'd have a lot more free space if you just dropped the database.

you could just tell your boss that it got irreversibly corrupted, and since you don't have a backup, that should be fine, right?

I mean, they probably expect to lose the data entirely one day through some mishap, since you don't have backups.

read: maybe you should think about creating a backup.|||A brief synopsis for those just now joining our story:
1st Skydiver: How can I shrink my parachute?
Instructor: Don't shrink your parachute.
1st Skydiver: I don't have enough room in my pack for it.
Instructor: Get a bigger pack.
2nd Skydiver: Try cutting a big wedge out of one side.
Instructor: Don't shrink your parachute. It is as big as it needs to be.
1st Skydiver: My pack has plenty of space. I can still fit a sandwich in it for lunch.
2nd Skydiver: ...or you could just cut a large hole in the center. That would probably work too.
Instructor: Better make sure your reserve parachute is working before you do that.
1st Skydiver: I don't have a reserve chute. I told you, I don't have enough space in my pack.
Instructor: Geronimo!|||Dude: They have company policies about data retention but you don't have a backup? That has GOT to take the cake.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.

I dont know what is full backup. BTW, i told that we dont take backup of that older database. If u had the same problem, how u solved it? And I am not allowed to take backup of that database.|||Read posts 61 (http://www.dbforums.com/showpost.php?p=6284483&postcount=61) and 66 (http://www.dbforums.com/showpost.php?p=6284560&postcount=66) :)|||Wow, this was fun to catch up on after a week on the beach, thanks guys! It's a relief to know that the good threads have staying power.

After n posts saying "I can't do anything you guys suggest, but I still need you to tell me how to fix my issue", if the reader/contributor can resist the urge to throw up his/her hands in frustration, and if he/she can distance him/herself emotionally, a thread becomes almost a source of glee.

This is especially true when a parachuting analogy can be successfully applied.|||Where d'ya go? Stop by the corral and fill us insql

How to shrink log file.

Hi, I have a database in SQL 2000 server whose
Transaction log file size is over 5GB but dats file size
just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
is not frequently used and I wanted to shrink the size to
save some disk space.In the properties of this log
file, 'unrestricted growth' is enabled.
Could some one help me to do it.
Thanks and regards, JohnHi,
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
MCDBA
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||backup transaction log
and then run dbcc shrinkfile(x_log, TRUNCATEONLY)
The truncateonly causes the unused space to be released to the Operating
System (Check oout BOL for dbcc shrinkfile)
--
Olu Adedeji
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||Yes it worked. Thanks a lot.
>--Original Message--
>Hi, I have a database in SQL 2000 server whose
>Transaction log file size is over 5GB but dats file size
>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>is not frequently used and I wanted to shrink the size
to
>save some disk space.In the properties of this log
>file, 'unrestricted growth' is enabled.
>Could some one help me to do it.
>Thanks and regards, John
>.
>|||>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 20GB but mdf file size
>>just 4GB. That data base
>>is frequently used and I wanted to shrink the size
>to
>>save some disk space.Moreover I want to delete the old
transactions of this particular log file. pls guide me how
to do this.
Regards
Triveni|||Hi!!
I have a database of around 116GB and somehow I lost the
> log file now if I want to do any transaction, it simply
> says log file is not available as I lost the log
> file.Kindly help me to get rid of this problem
any help would be highly appreciated.
>
>--Original Message--
>Yes it worked. Thanks a lot.
>>--Original Message--
>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 5GB but dats file size
>>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>>is not frequently used and I wanted to shrink the size
>to
>>save some disk space.In the properties of this log
>>file, 'unrestricted growth' is enabled.
>>Could some one help me to do it.
>>Thanks and regards, John
>>.
>.
>

How to shrink a SQL Server 7 device

Hi,
I have a seven year old SQL Server that has a 10GB Data Partition. Our main
DB is only around 6.5 GB but for some reason, the device size is almost 9GB.
The transaction log also has a lot of free space but but it is pretty small
so I'm not concerned.
If I remember correctly, I restarted the SQL Server last time and when the
server came up, the free space was truncated but I'm just wondering if
there's a way to do this without taking the DB offline.
I'm not sure why the datafile got so big in the first place as i checked the
backup logs and didn't see any failures.
Thanks for any suggestions.Ryan
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Ryan Gaudet" <RyanGaudet@.discussions.microsoft.com> wrote in message
news:2A4994B8-5C55-4805-AA34-D0E8BA9DBC4B@.microsoft.com...
> Hi,
> I have a seven year old SQL Server that has a 10GB Data Partition. Our
> main
> DB is only around 6.5 GB but for some reason, the device size is almost
> 9GB.
> The transaction log also has a lot of free space but but it is pretty
> small
> so I'm not concerned.
> If I remember correctly, I restarted the SQL Server last time and when the
> server came up, the free space was truncated but I'm just wondering if
> there's a way to do this without taking the DB offline.
> I'm not sure why the datafile got so big in the first place as i checked
> the
> backup logs and didn't see any failures.
> Thanks for any suggestions.

How to shrink a SQL Server 7 device

Hi,
I have a seven year old SQL Server that has a 10GB Data Partition. Our main
DB is only around 6.5 GB but for some reason, the device size is almost 9GB.
The transaction log also has a lot of free space but but it is pretty small
so I'm not concerned.
If I remember correctly, I restarted the SQL Server last time and when the
server came up, the free space was truncated but I'm just wondering if
there's a way to do this without taking the DB offline.
I'm not sure why the datafile got so big in the first place as i checked the
backup logs and didn't see any failures.
Thanks for any suggestions.
Ryan
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Ryan Gaudet" <RyanGaudet@.discussions.microsoft.com> wrote in message
news:2A4994B8-5C55-4805-AA34-D0E8BA9DBC4B@.microsoft.com...
> Hi,
> I have a seven year old SQL Server that has a 10GB Data Partition. Our
> main
> DB is only around 6.5 GB but for some reason, the device size is almost
> 9GB.
> The transaction log also has a lot of free space but but it is pretty
> small
> so I'm not concerned.
> If I remember correctly, I restarted the SQL Server last time and when the
> server came up, the free space was truncated but I'm just wondering if
> there's a way to do this without taking the DB offline.
> I'm not sure why the datafile got so big in the first place as i checked
> the
> backup logs and didn't see any failures.
> Thanks for any suggestions.

How to shrink a SQL Server 7 device

Wednesday, March 28, 2012

how to shrik log file

hi,
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
Larabackup the log first
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Lara" wrote:

> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>|||Lara,
If you go All Tasks->Shrink Database and then there choose between data file
or log file. Just a faster option(if we're talking about the log file)
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> backup the log first
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Lara" wrote:
>|||http://www.aspfaq.com/2471
"Lara" <lara@.hotmail.com> wrote in message
news:OIYF6KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>|||Have a look at
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Lara" <lara@.hotmail.com> wrote in message
news:OIYF6KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>|||You can backup the log with the no_log option. For example:
BACKUP LOG [DATABASE NAME] WITH NO_LOG
DBCC SHRINKFILE ([DATABASE NAME], 2)
The active portion of the log cannot be trucated. Keep this in mind, if you
are attemping to trucate in the middle of the day with open transactions or
are attempting to truncate the log during a very large nightly transaction.
If you frequently fill up a large log between trucates, then you may not
want to shrink it down to the absolute minimal size. Keeping the log file
large prevents the need for it to be extended and improves performance.
"Lara" <lara@.hotmail.com> wrote in message
news:OIYF6KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>

Monday, March 26, 2012

How to show all tables info in Task Pad?

In the table view in the Task Pad view, it lists the number of rows and size of each table, which is great, however it only lists the first 25 tables or so and there is no scroll function.

1. Does anyone know how I can see this info in Task Pad for all tables, without having to use the search function and look up 200+ tables one-by-one?

2. Does anyone know of another utility or statement to run against the DB which will return this info all at once for all the tables?

Thanks.Task pad should show Next and Last options in the bottom of the page.
You can also get all user table info by executing this sql..
select * from information_schema.tables where table_type like 'BASE TABLE'|||First, in TaskPad, there is no next or last button, second that line of code you gave:

select * from information_schema.tables where table_type like 'BASE TABLE'

Did not return the # of rows and KB size of all of my tables.

THis is what I am looking for.

Anyone else know?

I ran the "SP_help" and "SP_tables" stored procedures, but they don't return the table row count or size.|||I'd suggest:SELECT CAST(Coalesce(Sum(si.reserved) / 128.0, 0) AS DECIMAL(5, 2)) AS total_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS data_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid = 255 THEN si.reserved END)
/ 128.0, 0) AS DECIMAL(5, 2)) AS blob_mb
, CAST(Coalesce(Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN
si.reserved END) / 128.0, 0) AS DECIMAL(5, 2)) AS index_mb
, Object_Name(si.id)
FROM dbo.sysindexes AS si
GROUP BY si.id-PatP|||Pat,

What does that code do. Here was my output:

(20 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.|||sp_spaceused?

EDIT: Found this...

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename

SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO|||Pat,

What does that code do. Here was my output:

(20 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.Change the 5s to 15s and try again.

It shows some interesting space observations, by table.

-PatP|||pat,

That returned data, but the data_mb figures seem to be close to half of the actual size. For example, the size of a table from TaskPad is 79656 KB and your query generates 38.94 MB.

Is this what is expected? Is the data_mb column the table size?

Thanks for the help, it is greatly appreciated.|||Brett,

Wonderful!!!!!!!!!!

That was it!!!!!!

Thanks a million!!!!!!|||Does my total match the taskpad total?

-PatP

Wednesday, March 21, 2012

how to setting report viewer to view exactly as VS preview

hi all..I'm new here and got a problem, and i hope you guys can help me.
My problems:
I want to create one card , size : height =2.25, width =4.5 and it should be
on 2 page because I want to make it front and back.
i 'm using rectangle and textbox, and i set the page break after first
rectangle . When i preview the card, it shows 2 pages, one front and one back,
as it should be.
when i preview using print layout at VS, it became 5 pages. I already setting
using
[(Report.PageWidth - Report.LeftMargin - Report.RightMargin)>=Body.Width] but
it won't do.
And one more thing, when preview it on browser (report viewer), it shows one
full page, not the size of the card. So how can I make the preview setting
only preview the card exactly like the layout of the card, not full screen?
any help will be appreciated. thank you.the HTML version is optimized for a web rendering and the size of the pages
will be different.
but if the user try to print or export in pdf, then the layout will follows
your needs.
maybe you can try to change the interactive size of the report (not the page
size, the interactive size) to see if you can force the HTML page size.
"gmi" <u37770@.uwe> wrote in message news:78d60e78bb21e@.uwe...
> hi all..I'm new here and got a problem, and i hope you guys can help me.
> My problems:
> I want to create one card , size : height =2.25, width =4.5 and it should
> be
> on 2 page because I want to make it front and back.
> i 'm using rectangle and textbox, and i set the page break after first
> rectangle . When i preview the card, it shows 2 pages, one front and one
> back,
> as it should be.
> when i preview using print layout at VS, it became 5 pages. I already
> setting
> using
> [(Report.PageWidth - Report.LeftMargin - Report.RightMargin)>=Body.Width]
> but
> it won't do.
> And one more thing, when preview it on browser (report viewer), it shows
> one
> full page, not the size of the card. So how can I make the preview setting
> only preview the card exactly like the layout of the card, not full
> screen?
> any help will be appreciated. thank you.
>

How to set up the transaction log file properties?

Hello Guys,
I have question concerning the log file size:
I have a DB that receives a massive amount of data every two days and
several Transaction (USP) and delete records are usually done on it.
During these operations, the log size goes up until 32 Mo.
the size of the .mdf is 9Mo and .ldf is 14Mo
The DB is set to FULL and a backup DB and LOG is performed.
I set up the autoshink as well. is it required?
Why the log file increase so much and how to set up correctly the
transaction log file properties?
Thanks on advance.
Inaina
> I set up the autoshink as well. is it required?
No, disable this option
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Why do you concern about that the log file is growing? That means that SQL
Server needs more virtual log files to deal with data. Make sure that you
have a free space enough for log file
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1169025984.073903.91700@.v45g2000cwv.googlegroups.com...
> Hello Guys,
> I have question concerning the log file size:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
>
> Thanks on advance.
> Ina
>|||On 17.01.2007 10:26, ina wrote:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
What unit is that? Megabytes? If so this is really a small DB you
should not worry about sizes in that area at all.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
As Uri pointed out, rather switch this off since you only waste
performance. The log will grow and shrink anyway so you can leave it at
the largest size.
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Because apparently you DB needs the space (you mention "massive inserts").
Kind regards
robert|||Thanks :)
mdf: 9 MO (Mega Octets)
ldf : 14 MO
yes it is small DB but before, as I did not know about backup LOG and
shrink log file, I had for the same ldf file a size of 13 Go, and this
is big.
Really thank you because I solved this problem and everything works
file now.
These information helped me a bunch. :)
Ina
Robert Klemme wrote:
> On 17.01.2007 10:26, ina wrote:
> > I have a DB that receives a massive amount of data every two days and
> > several Transaction (USP) and delete records are usually done on it.
> >
> > During these operations, the log size goes up until 32 Mo.
> What unit is that? Megabytes? If so this is really a small DB you
> should not worry about sizes in that area at all.
> > the size of the .mdf is 9Mo and .ldf is 14Mo
> >
> > The DB is set to FULL and a backup DB and LOG is performed.
> >
> > I set up the autoshink as well. is it required?
> As Uri pointed out, rather switch this off since you only waste
> performance. The log will grow and shrink anyway so you can leave it at
> the largest size.
> > Why the log file increase so much and how to set up correctly the
> > transaction log file properties?
> Because apparently you DB needs the space (you mention "massive inserts").
> Kind regards
> robert

Monday, March 19, 2012

How to set up font size and color

I would like to know how to set up the font size and color in expression
window.
Thank you!I used the following to format my table row group:
=iif( Fields!TotalType.Value ="MyValue","D0",iif(Fields!TotalType.Value
="YourValue","P0","C0"))
and this expression for the cell background color:
=iif( Fields!TotalType.Value <> "MyValue" ,"LightGrey",Nothing)
Just select the object and type the expression in the appropriate field in
the Properties window for that object. You should be able to use the same
sort of expression of the font.
"Michelle" wrote:
> I would like to know how to set up the font size and color in expression
> window.
> Thank you!|||Thank you, daw. It helps.
"daw" wrote:
> I used the following to format my table row group:
> =iif( Fields!TotalType.Value ="MyValue","D0",iif(Fields!TotalType.Value
> ="YourValue","P0","C0"))
> and this expression for the cell background color:
> =iif( Fields!TotalType.Value <> "MyValue" ,"LightGrey",Nothing)
> Just select the object and type the expression in the appropriate field in
> the Properties window for that object. You should be able to use the same
> sort of expression of the font.
> "Michelle" wrote:
> > I would like to know how to set up the font size and color in expression
> > window.
> >
> > Thank you!

How to set the REDO_QUEUE size?

I have found some article on the web talking about how to change the REDO_QUEUE size like "ALTER DATABASE [AdventureWorks] SET PARTNER REDO_QUEUE 100MB;"

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

When I run it, the result is "Incorrect syntax near 'REDO_QUEUE'."

I have query for the Books online and there is no REDO_QUEUE there.

I also found "The only supported value for a mirrored database is the UNLIMITED value" on the MS KB http://support.microsoft.com/kb/907741

Is this comment obsoleted?

Thanks.

You can not set the redo queue.

|||

Hi, since we want to limit the size that is occuiped by the db mirroring to prevent the computer "hang" due to no free hard disk space, is there any other way to set the size limit?

Thanks in advance.

Monday, March 12, 2012

How to set the height of the report body/table dynamically

Hi
I have a report of 8.5"(W) by 11"(H).The header and footer size are fixed.
The header size is 4.75 in and footer size is 2.375in.
In the report body I have a table where the data is dynamic.
The problem is when the data is small i mean like 2 or 3 columns it doesnt touch the footer .
If we have more than 10 columns or so it goes to the next page and even then it doesnt touch the footer beacuse the data stops there.
If we have like 4 columns or so in the table then it touches the footer.
I tried to replace the table with the list but I got the same problem there also.
I tried to place the table in a rectangle and it has the same problem.
I want the table to touch the footer no matter how much the data is .
Is there any work around for this problem?

I really appreciate your help and time
Thanks
Hobbs

Hello,

The table will always grow based on its content. We do not currentlly support "Fill To Page" functionallity. Do you want the table style (borders and background color) to fill the page? Did you try settting the style on the report body?

Thank you,

Nico

|||

Hi Nico

Thanks for the reply.I tried the boderstyle of body and the table but it stops wherever the data ends.

I can send you the rdl file or the exported pdf if u want a take look at what I am talking about.And also I have a the bordstyle of the table column set to solid.even that one stops with the last data.

I was thinking if we have something like calulcating the line height or may be fill the gap after the table somehow dynamically then only fix it.

Thanks

Hobbs

|||

Did you solve it? I've got the same problem. I've got a report that needs border around it. If i use body's border then i can see the report correctly in report manager or after rendering it to pdf. But when i try to print from report manager right hand side border is missing. Printing of the report from rendered pdf file is fine. I tried few things like, created bland report with body only with border style to solid. I could see the border and print the border correctly. Then i added page header/footer and suddenly the right hand side border was missing. I think its bug. check this link http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=115351. I checked all the margin and layout setup everything is correct. Don't know how to get it working?

Regards,

Vivek

Friday, March 30, 2012

how to shrink Trx Log

how to shrink Trx Log

how to shrink Trx Log

how to shrink the size of tempdb other than reboot the server

how to shrink the logfile

how to shrink the logfile

how to shrink the logfile

How to Shrink SQL Server database

How to shrink log file.

How to shrink log file.

How to shrink a SQL Server 7 device

How to shrink a SQL Server 7 device

How to shrink a SQL Server 7 device

Wednesday, March 28, 2012

how to shrik log file

Monday, March 26, 2012

How to show all tables info in Task Pad?

Wednesday, March 21, 2012

how to setting report viewer to view exactly as VS preview

How to set up the transaction log file properties?

Monday, March 19, 2012

How to set up font size and color

How to set the REDO_QUEUE size?

Monday, March 12, 2012

How to set the height of the report body/table dynamically

set affinity for SqlExpress

Blog Archive

About Me