August 7, 2015 at 4:39 am
[font="Arial"]
Hello
I'm searching for some help in order to shrink a Transactional Log file.
This is in a BMC Remedy environment. I'm not a DBA, but i had to learn something about SQL Server in order to perform at least
some of the basic function about a DB.
I made my first DB backup after 2 month from the DB creation but nobody said me that also a Transactional log backup has to be execuder regularly, do my Log file has grown until 37 GB, leaving me only 7,33 GB free on a 50 GB virtual Hard Disk (think that the "data" file is less than 5 GB ...).
So, i've attempted to find a quick solution before that Log file uses all the available disk space, stopping the database behaviour. I've read some articles from Microsoft KB and using some other Internet good sites and i've understood that there are some things that have to be executed periodically, for example the Transaction Log Backup.
So, i've applied that rule, scheduling once time a week the Data Backup and once time a day the Transactional log backup. Now my Log file increases just a few MB weekly .. A situation that can allow me to be secure for at most one year without problems.
I've also read that there is the possibility to shrink the Transactional Log file, and aalso to move it in another location. These are two things that before doing them i prefer to have some good suggestion from someone more expert than me.
In particular, i've read that the shrink operation can be performed:
a) after a good Data and Log backup has been performed
b) after having changed the DB Recovery model from Complete to Simple
What i don't understand is if i can proceed with the Shrink of Transaction Log using the "Release unused space" option or if i have to use the "Reorganize pages before releasing unused space" option? Or if i have to use nothing. These 2 options are visible from the SQL Management Studio GUI. If you use T-SQL this is a simple example that i've read from Microsoft:
USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO
So .. Does someone may give me some suggestion in order to proceed in a Secure way? What can happen if something goes wrong? What i should do in this case?
Many thanks in advance
Alessandro Feltrin
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
August 7, 2015 at 6:38 am
You don't need to switch it to simple, and the two options (reorganise or release unused space) are only for shrinking data files.
The command you have there is almost right. Replace the first parameter with 2 (the file ID of the log file) and the second with the size in MB that you want the log to be after the shrink
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
August 7, 2015 at 8:24 am
[font="Arial"]
I have attempted to make a simulation using the SQL Management Studio, so that DB name and parameters should be the right ones).
What it write on the Script is the following:
USE [ARSystem]
GO
DBCC SHRINKFILE (N'ARSystem_log' , 0, TRUNCATEONLY)
GO
Does this sounds better than before? I don't see the new size ... Or the SQL Mgmt Studio has forced "0" as the new size? Does this seems to be more similar to the "release unused space" option?
I've read that the only way to specify a New size for the compacted file is to select the option "Reorganize Pages before releasing unused space"?. But i've also read (and you are saying the same) that for the Log shrinking is not necessary to apply these two options ...
Please let me know ...
in addition ... Due to the fact that the DB Data file is less than 5 GB and the daily Log backups are each one more or less 120 MB per day, i feel that if i specify a new Log size i can use 5 GB again ... Now the Log File has grown to 37 GB and is free at 92%. Probably fixing it from 5 to 10 GB it has enough space to fill ...
What do you think about?
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
August 7, 2015 at 9:04 am
[font="Arial"]
In addition ... Why you are saying that it's not necessary to switch to "Simple"?
This is written more or less in all documents that i've found on Internet, both from Microsoft and from other sites.
I feel that probably i don't have understood fine which are the correct rules ...
Please let me know ...
Thanks a lot
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
August 7, 2015 at 9:21 am
[font="Arial"]
For example, please, see this link in the section B) Shrinking a Log file to a specified Target Size:
https://msdn.microsoft.com/en-us/library/ms189493.aspx
Probably i feel that this can be because is better to "break" and renew the Log chain.
What do you feel?
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
August 7, 2015 at 11:22 am
alessandro.feltrin (8/7/2015)
GODBCC SHRINKFILE (N'ARSystem_log' , 0, TRUNCATEONLY)
GO
Does this sounds better than before?
No. Your previous script was correct with the amendments I made. Replace the first parameter with 2 for the file ID, or you can use the file name as above. The second parameter should be the target size in MB, if you want 2.5GB, which is probably a good starting point for a log for a 5GB database, then you'd put 2500 as the second parameter. That's all you need.
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
August 7, 2015 at 11:25 am
alessandro.feltrin (8/7/2015)
In addition ... Why you are saying that it's not necessary to switch to "Simple"?
Because it's not.
This is written more or less in all documents that i've found on Internet, both from Microsoft and from other sites.
Then you certainly haven't read my book on log management. And no, it won't be in every document because it's not true.
Simple recovery breaks the log chain, limits the ability to do point in time restores (the things you're doing log backups to allow) and hence can result in embarrassing conversations about why there's a 20 hour data loss happening.
To shrink you need free space in the log. Per your first post, since implementing log backups, you have free space in the log. Hence you can shrink and the file will reduce in size
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
August 7, 2015 at 11:27 am
alessandro.feltrin (8/7/2015)
Probably i feel that this can be because is better to "break" and renew the Log chain.
It's not better to break the log chain. There's a reason we have an unbroken log chain, and it's for restoring the DB in the case of a disaster.
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
August 13, 2015 at 3:50 am
[font="Arial"]
Ok Gail ...
Now all sound good .. I've understood some things that were not so clear for me.
Tomorrow i will attempt to apply what you've suggested me for the DB Shrink, during a planned change ...
In effect i've seen that the File ID is reall "2" as you said.
I've typed the command:
SELECT FILE_IDEX((SELECT TOP(1)name FROM sys.database_files
WHERE type = 1))AS 'File ID';
GO
in the Query panel and i obtain "2" as File ID value.
Another thing, Gail ...
Actually the Data File (file .mdf) and the Trans.Log file (file .ldf) lie on the same disk of the SQL Server.
I feel that can be a good thing to move the Log file into a dedicated disk of the same Server.
For what i have read i have to close communication from all systems that normally use the DB, then use this
procedure (i have already used the real DB name. Where there is the word "N'ARSystem'" i feel that i can write "2" that is the File ID):
USE master;
GO
-- Return the logical file name.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'ARSystem')
AND type_desc = N'LOG';
GO
ALTER DATABASE ARSystem SET OFFLINE;
GO
-- Physically move the file to a new location.
-- In the following statement, modify the path specified in FILENAME to
-- the new location of the file on your server.
ALTER DATABASE ARSystem
MODIFY FILE ( NAME = ARSystem_Log,
FILENAME = 'C:\NewLoc\ARSysLog.ldf');
GO
ALTER DATABASE ARSystem SET ONLINE;
GO
and then i have to perform a control that all things are good:
USE master;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'ARSystem')
AND type_desc = N'LOG';
What do you feel about?
Pls let me know ...
Thanks in advance.
Alessandro
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
August 13, 2015 at 4:07 am
Do the ALTER DATABASE before you take the DB offline.
1) Run Alter Database ... Modify File
2) Take the DB offline
3) Physically move the files
4) Bring the DB online
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
August 13, 2015 at 6:50 am
[font="Arial"]May have sense to set the DB in Single User mode, during both operations?
This may block possible undesiderable access attempts to the DB ... I feel
Alessandro
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
August 13, 2015 at 7:09 am
Unnecessary. When you run the ALTER DATABASE to take it offline, specify ROLLBACK IMMMEDIATE, that'll disconnect everyone
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
August 14, 2015 at 3:47 am
[font="Arial"]
HEllo ... I did what you said and the Shrink operation was good.
The moving to another location has an error ...
Seems thst it don't recognize the name as if it is weong, but i have checked and it seems written good.
The error is the following:
Messaggio 5120, livello 16, stato 101, riga 1
Impossibile aprire il file fisico "L:\MSSQL12.MSSQLSERVER\MSSQL\TransactionLog\ARSysLog.ldf". Errore del sistema operativo 5: "5(Accesso negato.)"
Errore di attivazione del file. Il nome di file fisico "L:\MSSQL12.MSSQLSERVER\MSSQL\TransactionLog\ARSysLog.ldf" potrebbe non essere corretto.
Messaggio 5181, livello 16, stato 5, riga 1
Impossibile riavviare il database "ARSystem". Ripristino dello stato precedente in corso.
Messaggio 5069, livello 16, stato 1, riga 1
Istruzione ALTER DATABASE non riuscita.
Have you some suggestion? How can i return to the previous location? Now i am no able to set the DB Online ...
Please give me a help
Thanks
Alessandro
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
August 14, 2015 at 4:44 am
The error is "Access is denied". The SQL Server service account does not have rights to read the file in the new location. You need to give it full control of file and folder.
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
August 14, 2015 at 5:02 am
[font="Arial"]
Yes Gail, i've thought something similar .. In any cases i've preferred to execute a new query defining the old path for the ldf file. I will attempt again next week, and i will open for this activity a new change.
In any cases now seems that DB is running again end the Log file has been restricted to 3,7 GB. The disk has now 75% of available space.
I feel that a good thing i should do can be to perform a Log Bckup more frequently ... Now i have scheduled it daily, at 7 AM, while the DB backup start once a week at Sunday at 4 AM.
The system is a Trouble Ticketing system so i feel that making a Log backup every hour should be enough, leaving the Complete backup once a week.
For the moment i thank you very much for all the suggestion you gave me. I am not a DBA so i prefer to move my steps in this field with caution ... Maybe asking to someone that can give some help.
Thanks again, Gail. I will let you know about my progresses.
Ciao from Italy
[/font]
Alessandro Feltrin
Information Security Professional
CISA Certified
ITIL v.3 Foundation Certified
BooleServer 3.2 Certified
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply