[MS SQL Server] Not able to shrink transaction log file |
- Not able to shrink transaction log file
- Howto. ..Load 2 columns from excel file into table with corresponding columns ?
- Can I strictly limit access to database ?
| Not able to shrink transaction log file Posted: 31 Mar 2013 09:55 PM PDT Why am i not able to shrink the transaction log file of my database even though there's 91% free space in it?I'm using the following script for testing purposes:/* FULL Recovery and Log File Growth */USE [master]GO-- Create Database SimpleTranIF EXISTS (SELECT name FROM sys.databases WHERE name = N'SimpleTran')BEGINALTER DATABASE [SimpleTran] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE [SimpleTran]ENDGOCREATE DATABASE [SimpleTran]GO-- Set Database backup model to FULLALTER DATABASE [SimpleTran] SET RECOVERY FULLGOBACKUP DATABASE [SimpleTran] TO DISK = N'D:\Backup\SimpleTran\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO-- Check Database Log File SizeSELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMBFROM sys.master_filesWHERE DB_NAME(database_id) = 'SimpleTran'GO-- Create Table in Database with TransactionUSE SimpleTranGOIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[RealTempTable]') AND TYPE IN (N'U'))DROP TABLE [dbo].[RealTempTable]GOCREATE TABLE RealTempTable (ID INT)INSERT INTO RealTempTable (ID)SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowIDFROM sys.all_objects aCROSS JOIN sys.all_objects bGO-- Check the size of the DatabaseSELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMBFROM sys.master_filesWHERE DB_NAME(database_id) = 'SimpleTran'GO-- Take Full BackupBACKUP DATABASE [SimpleTran] TO DISK = N'D:\Backup\SimpleTran\SimpleTran.bak' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO-- Run following transaction multiple times and check the size of T-LogINSERT INTO RealTempTable (ID)SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowIDFROM sys.all_objects aCROSS JOIN sys.all_objects bGO-- Check the size of the DatabaseSELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMBFROM sys.master_filesWHERE DB_NAME(database_id) = 'SimpleTran'GO/*Now run following code multiple times.You will notice that it will not increase the size of .ldf file but will for sureincreasethe size of the log backup.*/-- Second Time-- STARTBACKUP LOG [SimpleTran] TO DISK = N'D:\Backup\SimpleTran\SimpleTran.trn' WITH NOFORMAT, NOINIT, NAME = N'SimpleTran-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO-- Run following transaction and check the size of T-LogINSERT INTO RealTempTable (ID)SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY a.name) RowIDFROM sys.all_objects aCROSS JOIN sys.all_objects bGO-- Check the size of the DatabaseSELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMBFROM sys.master_filesWHERE DB_NAME(database_id) = 'SimpleTran'GO-- END |
| Howto. ..Load 2 columns from excel file into table with corresponding columns ? Posted: 01 Apr 2013 01:00 AM PDT Hello,Since I'm not a programmer and hopefully there's a simply way...I need to load one column from an excel file into a database table while trying to match the corresponding columns :unsure:1) I have 3 columns in the excel with a server , account and account description column 2) The database table has these same columns but the account description is BLANK 3) I need to populate the account description from the excel from the corresponding row.I hope i'm clearmany thx |
| Can I strictly limit access to database ? Posted: 31 Mar 2013 06:07 AM PDT Hi,I'm new in SQL Server administration however I need to make some major modifications in my SQL Server database. There are several applications from different places access tables. I've already called them not to use apps during process but I need to be sure nothing happens by mistake. So, I want to know if there is an option to restrict any access from outside during my work except my access. As I also develop a user interface by VS, I will need to have access through my VS too (Visual Studio is installed on the SQL Server computer so it will be a local access).Thanks in advance for helps |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment