July 28, 2008 at 3:05 am
Gents,
I'm trying to write the SQL script that shrink all the user databases on the instance. But unfortunately what I have written so far is not working properly please. Can some one please look at this and possibly find the bug inside. I'll really appreciate that.
Many Thanks, Basit
CREATE PROC [dbo].[ShrinkDB] ( @NewSize int )
AS
SET NOCOUNT ON
DECLARE
@MaxMinint
,@OriginalFileSizeint
,@Countint
,@StartTimedatetime
,@TruncateLogvarchar(255)
,@dbNamenvarchar(50)
DECLARE dbNames_cursor CURSOR
FOR SELECT [NAME] FROM dbo.sysdatabases WHERE NOT [name] IN ('master', 'model','msdb','tempdb')
OPEN dbNames_cursor
FETCH NEXT FROM dbNames_cursor INTO @dbName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
DECLARE @logName nvarchar(50)
EXEC(' USE [' + @dbName + '] ')
SET @logName = @dbName + '_log'
SELECT @MaxMin= 2
SELECT
@OriginalFileSize = SIZE
FROM
sys.sysfiles
WHERE
name = @logName
SELECT 'Original Size OF ' + DB_NAME() + ' LOG IS '
+ CONVERT (VARCHAR(50), @OriginalFileSize)
+ ', 8K pages OR ' + CONVERT(VARCHAR(50), (@OriginalFileSize * 8 / 1024)) + 'MB'
FROM
sys.sysfiles
WHERE
name = @logName
CREATE TABLE DumpTrn (DumCol CHAR(8000) NOT NULL)
SELECT
@StartTime = GETDATE()
,@TruncateLog = 'BACKUP LOG ['+ DB_NAME() +'] WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@logName, @NewSize)
EXEC (@TruncateLog)
WHILE
@MaxMin > DATEDIFF (mi, @StartTime, GETDATE())
AND
@OriginalFileSize = (SELECT FROM sys.sysfiles WHERE [name] = @logName)
AND
(@OriginalFileSize * 8 / 1024) > @NewSize
BEGIN
SET @Count = 0
WHILE ((@Count < @OriginalFileSize / 16 ) AND (@Count < 50000))
BEGIN -- UPDATE
INSERT DumpTrn VALUES ('Fill Log') -- Because it IS a char field it inserts 8000 bytes.
DELETE DumpTrn
SET @Count = @Count + 1
END -- INNER loop
EXEC (@TruncateLog) -- See IF a trunc OF the log shrinks it.
END-- OUTER loop
SELECT 'Final Size OF ' + DB_NAME() + ' LOG IS '
+ CONVERT(VARCHAR(30), ) + ', 8K pages OR '
+ CONVERT(VARCHAR(30), ( * 8 / 1024)) + 'MB'
FROM
sys.sysfiles
WHERE
[NAME] = @logName
DROP TABLE DumpTrn
END -- END of IF statement
FETCH NEXT FROM dbNames_cursor INTO @dbName
END -- End of OUTER Loop
CLOSE dbNames_cursor
DEALLOCATE dbNames_cursor
SET NOCOUNT OFF
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
July 28, 2008 at 3:24 am
Why are you shrinking your databases in the first place? It is not something you should be doing on a regular basis. They will just grow again as soon as someone uses them and the database needs some free space inside to work properly.
Shrinks cause bad index fragmentation and, if paired with autogrow can cause external (file-level) fragmentation as well.
See - shrinking databases[/url]
Be sure to read the two articles linked towards the bottom of that too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 4:07 am
We need to run this as job montly after the month end. As I am working for big financial organization and this is the business requirement. I know what are the advantages and disadvantages of Shrinking the database. Also We only going to shrink thoes databases that log file size is greater than 20GB. Can you please help.
Cheers,
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
July 28, 2008 at 4:14 am
I do hope you're taking a full backup afterwards, since you're also breaking your log chain doing this.
When you say "It's not working" What do you mean? Error? Not doing what's expected? Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 4:20 am
Yes we are taking the full backup of the databases before we run this job. So this is not the problem the problem with this script is its not working as I expect and I cannot find the error in this script actually I want this script to shrink the database log of all user databases one by one if its greater than 20GB. But it does not happen. Can you please advise.
Thanks and Cheers.
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
July 28, 2008 at 4:50 am
Actually, you want to take a full backup after running this, as you are truncating the log and breaking the recovery chain. After running this you will not be able to do a point in time recovery until you take another full/diff backups.
What is it doing that it shouldn't be, or what's it not doing that it should?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 6:05 am
Gilla Monster, I dont know why you are trying to change the subject of my post. Actually what I requested is that " IF SOME ONE CAN LOOK AND FIX THIS SCRIPT SO THAT IT CAN SHRINK THE LOG OF ALL USER DATABASES." Please only comment on this if you can. Also please do not try to comment on any thing else.
Thanks and Cheer
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
July 28, 2008 at 6:21 am
alimcitp (7/28/2008)
Gilla Monster, I dont know why you are trying to change the subject of my post. Actually what I requested is that " IF SOME ONE CAN LOOK AND FIX THIS SCRIPT SO THAT IT CAN SHRINK THE LOG OF ALL USER DATABASES." Please only comment on this if you can. Also please do not try to comment on any thing else.Thanks and Cheer
Hey steady on there, Gail is giving you some very sound advice and you're being rather off. Unless you answer the questions she has asked its doubtless anybody will reply as there is not enough info
:Whistling: I would have thought with all the certification you have you would be able to trouble shoot this simple procedure yourself
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 28, 2008 at 9:44 am
Perry Whittle (7/28/2008)
alimcitp (7/28/2008)
Gilla Monster, I dont know why you are trying to change the subject of my post. Actually what I requested is that " IF SOME ONE CAN LOOK AND FIX THIS SCRIPT SO THAT IT CAN SHRINK THE LOG OF ALL USER DATABASES." Please only comment on this if you can. Also please do not try to comment on any thing else.Thanks and Cheer
Hey steady on there, Gail is giving you some very sound advice and you're being rather off. Unless you answer the questions she has asked its doubtless anybody will reply as there is not enough info
:Whistling: I would have thought with all the certification you have you would be able to trouble shoot this simple procedure yourself
I am going to side with both Gail and Perry here - what you are trying to automate is not a GOOD thing and really shouldn't be done. With that said, you need to understand a few things before you move forward:
1) BACKUP LOG database WITH TRUNCATE_ONLY will be deprecated in future versions, so this will definitely not work after an upgrade (TRUNCATE_ONLY is not recognized in SQL Server 2008)
2) The replacement for the above is to switch to SIMPLE recovery, shrink the file, and then switch back to FULL. NOTE: This will break the log chain - so you really, really, really need to perform a backup immediately after this step. If not, every transaction log backup you attempt after this will fail.
3) The order you are trying to perform this in is wrong. See number two above, the steps are:
a) Backup database (BACKUP DATABASE...)
b) Switch database to SIMPLE
c) Shrink the LOG file
d) Switch database to FULL recovery
e) Backup database
f) Backup transaction log
I will restate that this is not a GOOD idea, especially since you are looking at doing this every month. That means, every month your log file is going to grow back to what it needs (+20GB) to perform the month end process. Since you have to have that space available for the logs to grow anyways - why do you need to shrink them? What is the purpose of shrinking the log files?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 28, 2008 at 1:04 pm
Jeffrey Williams (7/28/2008)
I am going to side with both Gail and Perry here
not to mention the tone with which the words echoed to whom i would class as a well respected and competent user of the forum.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 28, 2008 at 1:58 pm
Fully agreed - Either respond politely and answer the questions or you will never receive any sort of assistance.
July 29, 2008 at 8:46 am
Dont need to be rude. I have fixed my script without any of your help. Any way I want tell you that this is the company requirement. I know how to manage the database and no body needs to tell what to do or what not to do unless asked. I believe this forum is to help people. I have asked the simple question in my post that CAN SOME ONE PLEASE HELP WITH THE SCRIPT PROVIDED and in response I got suggestions why you want to do that? You can do this you can do that? and actually no one did bother to help me with that problem and this indicates that nobody understands that script. One Suggestion only reply to the posts which you are able to answer and DO NOT answer the post which you cannot understand.
Thanks for no help
Good Day, Ali
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
July 29, 2008 at 9:05 am
The problem with the script is that it was written by someone who doesn't understand flow control, doesn't understand the scope-limitations of dynamic SQL and doesn't understand code sequentiality.
I see at least a dozen logic, coding and procedural errors in it.
Whoever wrote it should not be allowed to administer databases till they've learned a little more. That person is placing the whole business at risk, and their manager needs to understand that this ignorance and attitude is making it possible that they will actually end up losing a month's data with no way to get it back. None.
I don't know who wrote that script for you, but don't trust that person. They think they know more than they actually do. From the code, I'm going to hazard a guess that the person is a procedural programmer (probably Visual Basic, maybe something else like that), who hasn't yet learned proper SQL programming.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 9:14 am
Thanks for your suggestions but this is not me who written the script this is written by some one else in the company and that is why its really complecated for me to understand this as I am not the SQL Developer. I have written my own script to shrink the logs now so I am not worried about this script any more. Any way thanks for your comments at least you are the first person who answered my post properly.
Cheers, Ali
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
July 29, 2008 at 9:17 am
alimcitp (7/29/2008)
Thanks for no help
If you read all of my last post you would have seen a request for more information on exactly what was wrong with it. Had you answered that, I'd have happily helped you fix your script. However I'm not going to debug something knowing only that "I want this script to shrink the database log of all user databases one by one if its greater than 20GB. But it does not happen."
'It does not happen' is not enough information. You know your system, I don't, I can't see the errors and I can't see what is or is not happening.
You wouldn't take your car to the mechanic and tell him "It's not working properly", would you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply