I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
Hello,I tried to make what one reply advice to recover the disk space afterDROPed columns but it doesn't work. I did DBCC DBREINDEX to the tablebut nothing changes.Any other solution?Ignacio
View 5 Replies View Relatedcurrently stuck with an issue where I need to reclaim the unused table space in SQL server.
ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size.
Similarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?
I am considering some disaster recovery scenarios.
Lets assume my MDF is gone - the disks are dead.
The LDF is on a different disk channel. Lets assume its fine.
Can I make a "final" TLog backup from the "good" LDF file?
Maybe copy some-earlier-MDF file into place, would that enable a TLog backup from the LDF file?
'Coz if I can then I could use that as a route to getting a zero-loss recovery - make a final TLog backup, and then restore the whole lot from last FULL + All TLog backups thereafter.
Kristen
Good morning,
We get frequent (1 out of 3) "sqlmaint.exe failed" errors during backups for one of our larger databases (40GB). So i removed the maintenance plan and put in a custom backup job to get a better error msg in the log:
BackupMedium::ReportIoError: write failure on backup device 'F:MSSQLmydb_2008_1_3_21_45.BAK'. Operating system error 112(There is not enough space on the disk.).
However, we have PLENTY of space on disk:
LOG DISK - NTFS - used space = 2GB, free space = 81GB
BACKUP DISK - NTFS - used space = 29GB, free space = 82GB
So even if it doubled in size, there would still be 50GB free.
Any ideas?
Thanks!!
What is the percentage of FREE disk space that is needed for a backup? I have backups that are failing with no disk space errors. But there is enough disk. Does SQL Server need a percentage of free space all the time?
Thanks
geri
Hi All,
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
Thanks.
Hi All,
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
Thanks.
Is it possible to reclaim space after rebuilding indexes(shrinking is not an option).
View 9 Replies View RelatedHi all
My Transactional log size increased to 39GB, it is in full recovery mode,
To regain the space, i have done the following
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINK_FILE (LOG_FILE_NAME,500)
But not able to regain the space in the hard disk.
No Transactional backups to truncate the log file were setup.
Can you please tell me why the space was released and what should i do further to clean up the sapce
best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION..I have a table that showed 62% Data, 7% unallocated, and 29% unused space.I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.What I do with this table monthly and what I believe is causing this unused space issue is this:Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
Hi all
Is there anyway to find out using TSQL for the used space VS what is available for TLOG file? Cause the GUI won't allow me [Database] > Report > Disk Usage. It said that Unable to display the report becuase the database has a compatibility level of 80 ?!?!
Thanks
Hi,This is probably an easy question for someone so any help would beappreciated.I have changed the columns in a table that where nvarchar to the samesize of type varchar so halve the space needed for them.I have done this a) becuase this is never going to be an internationalapplication, b) we are running out of space and c) there are 100million rows.I have done this with the alter table statement which seems to work butthe space used in the database hasn't altered.I'm presuming that the way the records are structured within the tablethere is just now more space free inbetween each page???Is there a way or re-shrinking just an individual table and free upsome of the space in there or am i missing the point somewhere?Thanks in advance,Ian
View 4 Replies View RelatedI have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.
If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?
We are using Sql server 2012. One of our production database has hight no of vlfs. We are planning to shrink the logfile to reduce the no of VLFs but the database is configured for logshipping.
What is the effect of logshipping when you shrink the logfile?
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
Can we backup a DB from SQL Server 2012 (Ent. edition) two node cluster to a local disk ?. Is it possible ?
View 7 Replies View RelatedI am getting occasional failures of a SQL Server 7.0 complete backup to disk on a production database. The errors seem to indicate that another process has the disk file open at the time of the backup. The errors contain the following texts : -
'Cannot open backup device'
'Operating System Error=32 Process cannot access file because it is being used by another process'.
The only other process that should access the disk file is an ARCserveIT scheduled job to copy the disk backup to tape but this is completing long before.
Any ideas or suggestions?
If I create an adhoc db backup that takes, say 30 miuntes to complete, should I suspend the tran log backups that run every 10 minutes, until the full backup is complete?
Drew
In SQL Server 2005, via the GUI, I wish to backup a database to an additional disk file (there is already an existing backup disk file for this database), so that I can have more than one backup. I've added the new disk file name, highlighted it, and clicked OK.
I get an immediate error (see below). Note, the 2nd error message is specifying the existing backup disk file, not the new one I'm attempting to create.
"Backup failed for Server 'WCS-DEV-TPA'. (Microsoft.SqlServer.Smo)"
"System.Data.SqlClient.SqlError: The volume on device 'D:Program FilesMicrosoft SQL ServerMSSQLBACKUPWCS_ADV_Longmont.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. (Microsoft.SqlServer.Smo)"
Does anyone know what causes this and how to correct it?
Hello,
I have an SQL 2005 maintenance plan that backups up the t-logs of my production db every 15 minutes. I want to begin log shipping over to a warm-standby secondary server. The network share, log shipping folder is not the same folder location as my maintenance plan folder. My question is, do I need to disable the maintenance plan for tlog backups in order for log shipping to be sucessful? Or will the dual backups to seperate backup folder locations cause a failure in the secondary server restore process?
I suspect the latter but wanted a second opinion.
Thanks.
My disk drive was full. So I deleted some records. Then I tried to shrink and it indicated available free space of around 5GB as indicated in the below image.
Once I ran the shrink for data file, though it completed successfully I didn't get the 5GB space as indicated. For log file shrink it shows available space of 200MB and when I run a log file shrink it does free up 200MB as indicated. So why is it not working with data file shrink? What am I doing wrong?
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file.
Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?
Recently maintenance was done removing some tables from the original filegroup in one drive of our SQL Server 2012 Standard Edition 64bits to another created on a separate physical drive. I was expecting the full amount of data moved to the secondary filegroup to show up as unused on the primary filegroup but that doesn't seem to be the case. Do I have to do anything after the move to release that space, not to disk, but to the database as unused?
View 2 Replies View Related
i have a very big database and number of people are working on it.. it's log file size is increaseing very day too much.. i am taking log back every 30mint...
i dont' know that wethare i need to truncate the log file after taking the log file backup or not.. i am taking differentail backup every day and full backup every week...
Please tell me do i need to trucate the log file to reduce the file size or i hv to leave as it....
Thanks..
Hello
I need to setup a compaq sever with 300 MB database, and will be adding around 600 records on a daily basis. Can someone help with how much disk space i should have on sqlserver, providing i have c: and d: setup.
Thank you in advance for all the help.
Paula
Hi All,
I have a server and it has C: D: F: I: Drives and all the system files are on C:Drive and and all the .MDF's and .LDF's(model,temp,master) are on the F: Drive and now I am running out of space on both(C: and F: Drives)
1. Can we add space to the C: and F: drives on the fly?.
2. Can I move the System databases ( MDF's and LDF's to some other drive)and if so, how do I do it?( Moving the databases ) and this is on the production database so when I have to do this.Will there be any impact.
Thanks in Advance,
Reddy
Does anyone know how to get the free disk space for all drives in many different SQL servers.
I need to populate a report (right now output in Excel) with the free disk space of all drives on all of my SQL servers.
I found xp_fixeddrives but that is specific for the server where it is executed.
Any help or pointers to a script in the archives is much appreciated.
Thanks.
I noticed something strange today. I was running a query using query analyzer on a large database (8.8 million records) and the disk space on the c: drive was dropping and eventually went to 0. Availalbe space on the c: drive is 10GB. The query did complete. SQL server and all the databases are on the d: drive. After closing the query results in query analyzer the disk space returned. Is this a concern and is there a way to change it to use the d: for whatever it is doing?
View 3 Replies View RelatedThis is my first attempt using SQL 2000 and DTS. I am importing an Access database using the DTS wizard. The process fails with a "Not enough space on temporary disk" error. There is definitely enough space on the physical disk. I don't have any limits on any folder sizes either. What "disk" is the error talking about, and how do I give it enough space. The database is relatively small, about 10MB. I believe the database was created using Access 97. Please help.
View 1 Replies View RelatedI need a way to get the used space for a physical drive, or even the total capacity for a drive to do some calculations.
I don't want to have to write my own native code either.
Is there a stored procedure that will give this to me?
I saw the xp_fixeddrives that will give the free space, but I need one of the other as well.
Any way to get this?
Thanks,
Jason
We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ???
Thanks so much for any help...
Nancy