April 16, 2019 at 11:38 pm
Rather than scripting this out, I just used the GUI, and did a right click on the properties, and change the initial size of the database.
I did get out of the Screen and unable to see the size change.
If I do the following I can not see a change in size.
SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, *8/1024 SIZE_MB
FROM MASTER.SYS.MASTER_FILES
WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'
Is the only way to change the size of the Initial Size for Tempb is that I have to use the ALTER command?
Or when I shutdown the services will I be able to see the size of the Tempdb.
Thanks
April 17, 2019 at 2:01 pm
Changing the initial size in SSMS just executes a DBCC SHRINKFILE. You may have tried to shrink to a size that it couldn't shrink to due to space in use. The term initial size is misleading though. This is a good article explaining the concept and how to find the actual minimum size:
Sue
April 17, 2019 at 4:01 pm
Great example, thank you.
April 17, 2019 at 4:20 pm
I was able to see the MINSIZE and this was 67 GIG and the intital size of the database was 200 GIG so the shrink went to the min size.
I did get an alert on the TEMPDB in the error logs.
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'
What is the best way to get this type of alert in the SQL Logs sent to me immediately. I can read the syslogs but that would run every x hour.
April 17, 2019 at 4:34 pm
You can create an alert for that error number which I think would be 1101, you can check your logs to verify. On the response, send the notification to whatever DBA operators or yourself, however you manage those.
Sue
April 17, 2019 at 4:43 pm
Thank you I shall look at the alert steps now.
April 17, 2019 at 6:50 pm
I added a few alerts, I am not able to find one alert for when auto growth occurs. Would you know what number event this is?
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645611(v%3dsql.105)
April 17, 2019 at 7:23 pm
I don't think there is a message for any autogrowth. You can find the messages by querying sys.messages - that's how I found the error number for the message you had. An alert needs to have the message logged for the alert to fire - so is_event_logged = 1 in sys.messages. You can change the logging of any of those using sp_altermessage
Anyway, growing really isn't an error so you won't find a corresponding message, message id. An alternative to that is to use WMI for an alert. WMI event alert is one of the alert types in the drop down when you are creating an alert in SSMS. There is a pretty good example of using WMI event alert for growth (exactly what you are working on) if you wanted to try that:
https://www.mssqltips.com/sqlservertip/3128/monitor-sql-server-database-file-growth-with-wmi-alerts/
The tokens in that example might be confusing - those are the ones with $(ESCAPE_SQUOTE) that look like:
body = ''File Name: $(ESCAPE_SQUOTE(WMI(FileName)));
Start Time: $(ESCAPE_SQUOTE(WMI(STartTime)));
You can leave those out for now while working on it. They give you more information on what database, what time, etc.
The other option to capturing this would be setting up a job to query the default trace looking for any of the growth events. It's doable but I'd probably try going with the WMI alerts since it's an option you may want to use for other monitoring, alerts moving forward. So a good thing to learn. You can get it working with WMI alerts.
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply