January 12, 2018 at 3:29 pm
quinn.jay - Friday, January 12, 2018 3:25 PMRandomStream - Friday, January 12, 2018 2:38 PMThat's a very courageous attempt on a Friday. I hope you understood the implications of the cleanup and the server is not mission critical. How much space did you reclaim from that?None, but now you're scaring me. It failed with that error, and I think it did nothing, certainly no space was reclaimed
If that folder is not taking up large amount of space (say, less than 5GB), I suggest you leave it alone for now and run the queries I provided and post some details. Based on the results, we could provide additional suggestions. Without seeing the system we can only guess what might be taking up space.
January 12, 2018 at 4:37 pm
RandomStream - Friday, January 12, 2018 3:29 PMquinn.jay - Friday, January 12, 2018 3:25 PMRandomStream - Friday, January 12, 2018 2:38 PMThat's a very courageous attempt on a Friday. I hope you understood the implications of the cleanup and the server is not mission critical. How much space did you reclaim from that?None, but now you're scaring me. It failed with that error, and I think it did nothing, certainly no space was reclaimed
If that folder is not taking up large amount of space (say, less than 5GB), I suggest you leave it alone for now and run the queries I provided and post some details. Based on the results, we could provide additional suggestions. Without seeing the system we can only guess what might be taking up space.
I ran all that code and there is so much sensitive info I have to chop out that it wont leave much useful info is the problem. I was able to whack a number of event view logs, to open more space, so I got a little bit more breathing room
January 13, 2018 at 12:03 am
quinn.jay - Friday, January 12, 2018 9:02 AMHappyGeek - Friday, January 12, 2018 12:38 AMIt is not clear as to your actual setup to know what to suggest: In terms of the SQL server logs and SQL Agent logs, that are titled "Archive #1" etc. if your concern is the size that they are growing to then you can recycle them on say a weekly (or daily) basis with an SQL job that runs the following:Use [master];
GO
SP_CYCLE_ERRORLOG
GO
USE msdb ;
GO
EXEC dbo.sp_cycle_agent_errorlog ;
GO
If you have database files on the OS partition you may wish to consider moving these too.I dont have db files on the OS partition, thankfully. I did run the code you shared, thanks, it seemed to only truncate the current log file, but and didn;t do anything to the other Archive# files. And then there those Windows NT logs, and the SQL Server Agent logs. There are so many with lots of details, it seems to be this as the slow growing files on the OS that I'm needing to trim with the right tool. and not go in with File Explorer and delete and screw up things. I say that, as I've made that mistake before.
No, it does not simply truncate the current error log, it closes the current error log and cycles the error logs in a similar fashion to a server restart, see these two articles for details:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql and
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-agent-errorlog-transact-sql.
...
January 13, 2018 at 2:01 am
Suggest you start by listing all the files on the C: drive by size and then address them in that order, here is a script that does the file listing
😎
CREATE TABLE #DIRLIST_C (D_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,D_TXT NVARCHAR(4000) NULL);
INSERT INTO #DIRLIST_C (D_TXT)
EXEC xp_cmdshell N'dir C:\ /S';
SELECT * FROM (
SELECT
DC.D_ID
,SUBSTRING(DC.D_TXT,1,17) AS TSTR
,SUBSTRING(DC.D_TXT,18,18) AS SSTR
,ISNUMERIC(SUBSTRING(DC.D_TXT,18,18)) AS NMBFLG
,CASE
WHEN ISNUMERIC(SUBSTRING(DC.D_TXT,18,18)) = 1 THEN CONVERT(BIGINT,REPLACE(REPLACE(SUBSTRING(DC.D_TXT,18,18),',',''),'File(s)',''),1) / POWER(2,20)
END AS XIZE
,DC.D_TXT
FROM #DIRLIST_C DC
WHERE DC.D_TXT IS NOT NULL
AND ISNUMERIC(SUBSTRING(DC.D_TXT,18,18)) = 1
) AS X ORDER BY X.XIZE DESC;
DROP TABLE #DIRLIST_C;
January 13, 2018 at 8:56 am
Curious to know why if there are no database files on the OS partition i.e. C: drive, you say in your first post you are shrinking transaction logs? If they are not there what difference does it make?
...
January 13, 2018 at 9:15 am
HappyGeek - Saturday, January 13, 2018 8:56 AMCurious to know why if there are no database files on the OS partition i.e. C: drive, you say in your first post you are shrinking transaction logs? If they are not there what difference does it make?
Suspect there are system db's, dump files, log files etc., would be surprised if otherwise
😎
One of the first thing to check is the location of the tempdb, if on the OS drive then add another file on a data drive and shrink the OS drive one. Core dumps can normally be deleted, update directories and temp stuff deleted and the paging file cut down to handful of Gbs.
January 13, 2018 at 10:02 am
Eirikur Eiriksson - Saturday, January 13, 2018 9:15 AMHappyGeek - Saturday, January 13, 2018 8:56 AMCurious to know why if there are no database files on the OS partition i.e. C: drive, you say in your first post you are shrinking transaction logs? If they are not there what difference does it make?Suspect there are system db's, dump files, log files etc., would be surprised if otherwise
😎One of the first thing to check is the location of the tempdb, if on the OS drive then add another file on a data drive and shrink the OS drive one. Core dumps can normally be deleted, update directories and temp stuff deleted and the paging file cut down to handful of Gbs.
+100, personally moved all, or those that can be, off the C: drive on to another drive, completely separate drive for tempdb, paging file would be own drive too if I had the capacity. Logs frankly are a nightmare but can be managed, hence my first suggestion.
...
January 13, 2018 at 8:55 pm
1. Easy 5 minute fix to get you out of the red: Run a tool to identify temp files, recycle folder, download folder, and other stuff that can be deleted off.
2. Even better if possible: Uninstall unneeded software from server. SSDT, SSRS, and other stuff are sometimes installed and not used.
3. Permanent fix: Repartition the local drive, reallocating space from D to C, but of course consider downtime and disaster recovery option if it fails.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 14, 2018 at 8:51 pm
Eric M Russell - Saturday, January 13, 2018 8:55 PM3. Permanent fix: Repartition the local drive, reallocating space from D to C, but of course consider downtime and disaster recovery option if it fails.
I'd recommend this option if you can swing it. Generally speaking, databases continue to grow over time because the data tends to grow. Windows files tend to grow as well, so getting the databases off the system drive should be a priority. Anything other than getting serious disk space (either with Eric's suggestion or installing more drives) is going to end up being a temporary fix and you're going to end up back in the same spot you are now again soon. Of course, a SAN with expandable volumes would be a great option if it's available to you, but I don't know if that's realistic. Whatever route you take, I'd suggest getting as much space as you can to get as much time as possible.
January 15, 2018 at 7:07 am
HappyGeek - Saturday, January 13, 2018 8:56 AMCurious to know why if there are no database files on the OS partition i.e. C: drive, you say in your first post you are shrinking transaction logs? If they are not there what difference does it make?
Because its something else that growing on a crowded server
January 16, 2018 at 5:51 am
Grant Fritchey - Friday, January 12, 2018 4:45 AMSounds like you may have data and transaction files on your system drive. Don't. Move those. You'll have to take the databases offline and do a detach/attach, but that will solve the problem. Shrinking files over and over is problematic.
don't detach\attach. Use the alter database modify file clause to alter the database file paths, take the db offline, move the files then bring it back online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 16, 2018 at 5:54 am
quinn.jay - Thursday, January 11, 2018 3:58 PMI'm running a MS Windows Server 2012 R2, and SQL Server 2016 SP2, the main HDD OS drive is split into two partitions, C:\ and D:\, with lots of space on D:\ and only 60GB total space on C:\ now with less than 5GB left, it's in the red.Besides using Minitool trade space around, a radical move, what all can I delete log file wise that keeps growing? I'm already shrinking the T log files each week, and I have found and deleted all the /tmp, /temp, cache, etc usual places.
How can I delete those old archive logs I see in the log viewer? I have found info on deleting it so I think, but its not it, I want to delete a large swatch of them. I see stuff like go to the database, right click, props, file, slect the log and then remove. Thats not removing those old archive logs I have fro mte h day this thign was stood up.
Any and all help is greatly appreciated.
Thanks
If there are user databases on the c drive move them to d drive.
60Gb is` a little on the small side for a windows 2012 R2 server system drive, check the page file setting and modify that too.
The c drive only needs a page file of about 400MB to 800MB for memory dump during BSOD
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 16, 2018 at 7:29 am
Shifting gears a bit, it's amazing to me how much junk Windows Updates leaves behind that you can't actually delete.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2018 at 7:53 am
Jeff Moden - Tuesday, January 16, 2018 7:29 AMShifting gears a bit, it's amazing to me how much junk Windows Updates leaves behind that you can't actually delete.
agreed, and when I started this, I thought it was more a SQL Server issue eating up space, but my DBs, and Logs, etc are located on other drives, not the OS drive. I have gone through and deleted all deletable junk, yet I also find that there are things left behind from old installs and such that your can't. plus, being a server, it has all sorts of logging and it is this that is the creeper on space. I admit, our companies rules on bare metal SQL Server boxes 60GB for C:\ OS and DB App is not much. Now I'm left with still considering a space swap with Minitool, and also clearing more out of the Windows events logs as a way to create enough space to deal with over time space creep. This actually makes for a good case to be running a virtual SQL Server, a those adjustments are less risky to make, and a lot easier to than a bare metal box.
January 16, 2018 at 10:30 am
I use tool on my personal PC to keep tabs on disk space allocation by folder. It would help in your case too.
https://jam-software.com/treesize_free/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply