![]() (Yeah, I had someone do that once.)Ä®ven though I donât use Extended Events as much as I probably should, in cases like this Iâm glad they are around and certainly see the benefit in using them. This can come in handy if you have a rouge developer who likes to shrink log files when he things no one is looking. To increase the size of a transaction log file In the development environment, on the File menu, choose Database, and then choose Alter. You can look up masterfiles/sysfiles (2k) or .sys.databasefiles (2k5/2k8) to get the logical name of the log. MODIFY FILE to change the size of database files, including LOG files.MODIFY FILE to change the size of database files, including LOG files. This means that the the above Extended Event session will also track any SHRINKFILE operations on a databaseâs data and log files. Your log file will grow naturally to the size that is required, assuming that sane autogrowth parameters are set (note that as TomTom suggested its probably better to start with a reasonable size rather than wait for autogrowth to take you there, robbing you of performance in the meantime). SQL 2K How to increase the size of a database (Enterprise Manager), How to shrink a database (Enterprise Manager) Long answer: you can use ALTER DATABASE. It tracks any changes in size to the data and log files. Use this event to monitor changes to the size of database files. Occurs when any of the data or log files for a database changes size. To increase the size of an existing file, increase the value in the Initial Size (MB) column for the file. Here is the description of the database_file_size_change event taken from the Extended Event wizard: ![]() However, if we take a look at the database_file_size_change event that we are using, we will find that it doesnât just apply to file growth. As per my understanding some of the reasons the transaction log file can grow are: Long running queries: select name, logreusewaitdesc from sys. The event itself is pretty light weight, it uses the database_file_size_change event to track size changes of the data and log files and records what caused those changes, including the user and T-SQL script used. SIZE 125MB, MAXSIZE 500MB, FILEGROWTH 25MB ) LOG ON ( NAME AnotherDBlog, FILENAME 'C:\Program Files\Microsoft SQL Server\MSSQL1050.SQL2008R2\MSSQL\DATA\AnotherDB. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |