I was performing dile shrinking using GUI. After more than 10 hours I got an error, saying that the shrink command failed.
Maybe a silly question but I am not able to see exact time when Error occurred in sql server?
I started shrink file job in SSMS using GUI.
After more than 10 hours I got an error message "Shrink failed ..."
But how can I see exact time when it happened? I would think it's one of the most important detail points.
The log doesn't even have a file for 2/8/2022. Why?
I tried to query error log and find the message error but no success as well.
So how to find out the time when shrink file stopped?
February 10, 2022 at 2:40 pm
It really does depend on the error, but you should look at the system_health extended event session. You can get errors there. Also, you can get queries that held locks and waits longer than 30 seconds. Between the two you might, important word, be able to see this. Otherwise, for some errors, say, the client disconnected, it just doesn't go to the error logs or system_health.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2022 at 2:44 pm
I would check the error log. The log is ONLY cycled if you force it to (I believe). It can be forced with a command or with a restart. The date on the log is the last modified date. So you want to open the log that was modified the most recently.
Now, I would strongly encourage you not to shrink your database files unless absolutely necessary, and even then I'd be cautious about shrinking them. The files grew to a specific size for a reason. So unless you just deleted a boatload of data and won't be adding that much data back in for a long time, they are likely to grow to those sizes again.
Also, had you shrunk the files using TSQL, you would have a little duration thing happening in the corner so you could have seen how long it ran for. It is another good reason to do things in TSQL rather than from the GUI.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
I was performing dile shrinking using GUI. After more than 10 hours I got an error, saying that the shrink command failed.
Maybe a silly question but I am not able to see exact time when Error occurred in sql server?
I started shrink file job in SSMS using GUI.
After more than 10 hours I got an error message "Shrink failed ..."
But how can I see exact time when it happened? I would think it's one of the most important detail points.
The log doesn't even have a file for 2/8/2022. Why?
I tried to query error log and find the message error but no success as well.
So how to find out the time when shrink file stopped?
Seriously, using the GUI to attempt a shrink that took at least 10 hours is a recipe for disaster.
I recommend shrinking a little at a time. Make sure you set SSMS to output the results to text instead of grids. Grids may exceed the available output and stop the process.
USE [YourDatabase]
GO
DECLARE @i int = <CurrentSize>
WHILE @i >= <Final Size> Begin
DBCC SHRINKFILE (N'Exception_log' , @i)
--Shrink by 1024 MB at a time.
SET @i -= 1024
END
GO
What caused the file to grow this large?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 10, 2022 at 4:50 pm
........... And understand the impact on your indexes from shrinking data files.
February 10, 2022 at 6:10 pm
we have a huge table that takes 80% of the data. Table has image datatype column which holds pdf documents. Recently we started to offload those documents to Azure blob. In order to accelerate backups and disaster recovery we decided to shrink data file, which currently has more than 1 Tb of free space.
February 10, 2022 at 8:19 pm
My opinion - storing PDF's in an IMAGE datatype column is just wrong. Mind you, I try not to use SQL for storing documents in general, but it sounds like you are working to fix that.
I think that your best bet is to follow Michael's advice and shrink it in small chunks. I would do it in 1 GB chunks as that should be quick (assuming that the free space is at the end of the data file) and repeat until it fails to shrink OR it hits your desired size. Once that is complete, you will probably want to rebuild your indexes (as homebrew01 indicated).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 10, 2022 at 10:48 pm
It seems no matter how small chunks I set, it progresses to around 98% and stucks.
Can it be because of LOB data ?
February 10, 2022 at 11:09 pm
It seems no matter how small chunks I set, it progresses to around 98% and stucks.
Can it be because of LOB data ?
does 98% get you down to what you need? Then stop.
Otherwise, expand the file by a few MB’s and try again.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 10, 2022 at 11:37 pm
It seems no matter how small chunks I set, it progresses to around 98% and stucks.
Can it be because of LOB data ?
does 98% get you down to what you need? Then stop.
Otherwise, expand the file by a few MB’s and try again.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 11, 2022 at 12:21 am
Thanks Michael.
The code you provided works much better.
One question, if I simply stop (terminate) the execution, would that be ok?
It will not create any problems?
February 11, 2022 at 12:25 am
Thanks Michael.
The code you provided works much better.
One question, if I simply stop (terminate) the execution, would that be ok? It will not create any problems?
No. it won’t cause problems.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply