MS Sql Server

“The Transaction Log For Database Is Full” Easy Steps to Resolve

Table of Contents Now let’s understand why does this The transaction log for database is full error get occurred and how to deal with it. Increase the Transaction Log file size You May Also Like Microsoft is a widely used database in windows server, also its be default SQL server with a Plesk control panel which is used on windows base server in hosting industries. Every database has its own property and limitations set when it gets created, many times it happens that these resources get exhausted after a certain period of time. And one of the commonly reported issues about this is “The transaction log for database ‘xxx’ is full”. When you reach your limitation of transaction log size you get this issue Now let’s understand why does this The transaction log for database is full error get occurred and how to deal with it. What is a transaction log file, it’s simply the record of the activities performed on the database and a complete track recode of it, It also uses in database recovery so you know what’s the importance of it, a Transaction log file (.IDF) keeps on increasing by the period of time as the transactions keep growing and in most case, if the database has any unidentified transaction its cause the log fie filled rapidly. In this post, we will be going to show you step by step actions to overcome “The transaction log for database is full” issue. There is two way of solving this issue. Shrink the Transaction Log Increase the Transaction Log file size Shrinking the SQL server shrink log file Many of us know about the shrinking option now let’s see how we can shrink the transaction log file. We can easily perform this action by using the SQL server management studio for shrinking the transaction file. Step 1 : Login into the SQL server management studio with “SA” user Step 2: Expand the databases > and select the database name which is having transaction log size full issue Step 3: Right Click on database name and go to Task > Shrink > Files Step 4:  Here, in this step, you have to select “File Type” as “Log” and “File Name” of the transaction log file. And then, you will be seeing a check box in name of release unused space. Click on the check box and this enables us to reduce the transaction log files space but it doesn’t delete any files to reduce the space. Step 5: You can find another check box option called “reorganize files before unused space”. When you select this option it will allow you to enter the value for shrinking the logs. If you use this option then this is permanent so by default, your process will be cleared. by doing this you mean to reduce the rows and to relocate on unallocated pages. Step 6: Here, you can choose the percentage left out for database shrinkage. The percentage count can be changed between 0 and 99. But this step is enabled only when you select 2nd option which is “reorganize files before releasing unused space”. Step 7: The third option you can go with is an empty file by migrating the data to other files in the same file group. From the same group, it moves the files to a different location after then, the open space file can be deleted. Increase the Transaction Log file size Another way to deal with this issue is to increase the actual size of the Log file to do so please follow the below steps Step 1 : Login into the SQL server management studio with “SA” user Step 2: Expand the databases > and select the database name which is transaction log size issue Step 3: Right click on the database and click on “Properties” Step 4: Go to the “Files”  section from the left-hand menu list as shown below and update the LOG size on the right-Hand Side under the Size(MB) section as shown below. Both ways are suitable to tackle this issue and can be used to fix it.  Alternatives for responding to a full transaction log include: Backing up the log. Freeing disk space so that the log can automatically grow. Moving the log file to a disk drive with sufficient space. Increasing the size of a log file. Adding a log file on a different disk. Completing or killing a long-running transaction.
Read more

How to shrink the transaction log

One thing that I see a lot of administrators ask about is transaction log size and how to truncate it. Log records that are not managed correctly will eventually fill up the disk causing no more modifications to the database. Transaction log growth can occur for a few different reasons. Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log. Log truncation frees up space in the log file so the transaction log can reuse it. Unless there is some kind of unexpected delay, log truncation will occur automatically after a checkpoint (if the database is in SIMPLE recovery model) or after a log backup (if the database is in FULL or BULK-LOGGED recovery model). MSSQLTips.com offers plenty of tips regarding transaction log truncation, but I’ll show you two ways to shrink the log. Shrink the log in SQL Server Management Studio To shrink the log in SSMS, right click the database, choose Tasks, Shrink, Files:   On the Shrink File window, change the File Type to Log. You can also choose to either release unused space, reorganize pages before releasing unused space, or empty file by migrating the data to other files in the same filegroup:   Shrink the log using TSQL If the database is in the SIMPLE recovery model you can use the following statement to shrink the log file: DBCC SHRINKFILE (AdventureWorks2012_log, 1) Replace AdventureWorks2012_log with the logical name of the log file you need shrunk and change 1 to the number of MB you want the log file shrunk to. If the database is in FULL recovery model you could set it to SIMPLE, run DBCC SHRINKFILE, and set back to FULL if you don’t care about losing the data in the log. ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE GO DBCC SHRINKFILE (AdventureWorks2012_log, 1) GO ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL **You can find the logical name of the log file by using the following query: SELECT name FROM sys.master_files WHERE type_desc = 'LOG' Another option to shrink the log using the FULL recovery model is to backup the log for your database using the BACKUP LOG statement and then issue the SHRINKFILE command to shrink the transaction log: BACKUP LOG AdventureWorks2012 TO BackupDevice
Read more

Import a CSV File Via SQL Server Management Studio

The SQL Server Management Studio is also known as SSMS, and it is a software application launched by Microsoft SQL Server 2005. The SQL Server Management Studio helps to configure, administer, and manage all the components within the server. The SSMS also includes both graphical tools and script editors that work with the features and objects of the server. The object Explorer feature of the SQL Server Management Studio allows the users to select, browse, and act upon any of the objects in the server from a single location. This feature has made the management of objects and features of the SQL database easy. Copying bulk information into the SQL Server Management Studio is a time-consuming process, and this can sometimes cause errors. You can use the CSV formatted files to copy such bulk information to the database. The SQL Server Management Studio tool has a built-in feature to import CSV formatted files, so there is no need for you to install any additional software. In this knowledge base article, you will learn the steps to import a CSV file into your database with the help of the Microsoft SQL Server Management Studio. Import CSV File Using SQL Server Management Studio Please make sure that a table is already available on the database before importing the CSV file. If there is no table available on the database, then you have to create a sample table in the SQL Server Management Studio tool. Once you have a table, Let’s understand how to import the data from a CSV file. First of all, Open the SQL Server Management Studio. Log in to the target database and right-click on the target database to open the Object Explorer. Please note that you should click the entire database and not any particular table inside that. From the Object Explorer, hover the Task option and find the ‘Import Data’ option. Select the ‘Import Data’ option. This step opens the ‘Wizard Introduction’ page. Click the ‘Next’ button to open the ‘Choose a Data Source’ page. From the ‘Choose a Data Source’ window, select the Data Source as ‘Flat File Source’ from the drop-down. Click the ‘Browse’ button next to the File name to open the Windows Explorer page. From the ‘Windows Explorer’ screen, Select the CSV formatted file that you want to import. After selecting the file, configure how you want to import the data into the database. Then, select the checkbox next to the ‘Column names in the first data row’ option. Make sure that the column names match the headings given in the first line of a CSV file you want to import. Click the ‘Column’ option from the left-hand side of the window and review the column names in the file. Then, click the ‘Advanced’ option from the left-hand side of the window to examine the advanced options, such as Data type, length of the String, and more. By default, the length of the string is 50. If your imported file has strings more significant than 50, click the ‘Suggest Types’ button to inspect all the columns in the file. By default, SQL Server only examines 100 rows, so you can click the ‘Suggest Types’ button to examine each column. This option points out errors during the inspection process itself. You can inspect the whole file or selected files using this method. After that, you can click the ‘Preview’ option from the left-hand side of the ‘Choose a Data Source’ window. You can review all the columns again for the last time before the import. After the review, click the ‘Next’ button. On the next page, select the destination database details, such as database name, server name, Authentication type, and destination database type. Click ‘Next’, and on the next page, you can see that the SQL server has selected the table on your behalf. But if it is not the case, you can create a table or select a different table from the list. Click the ‘Edit Mappings’ button to modify the table details. After that, click the ‘Next’ button. Now, the system prompts you to save the imported file as an SSIS package. If you don’t want to save the file as an SSIS package, then you can leave the option unchecked. Check the ‘Run immediately’ option and click the ‘Next’ button. The system prompts the verification screen. Review all the details, and if everything is fine, click the ‘Finish’ button to import the CSV formatted file to the selected destination database. So, this is how you can import a CSV file in SQL Server Management Studio. Conclusion The SQL Server Management Studio is also known as SSMS, and it is a software application launched by Microsoft SQL Server 2005. You can use the CSV formatted files to copy bulk information to the database by using the SQL Server Management Studio tool. The built-in feature of the SSMS tool helps you to import CSV formatted files. We hope that this knowledge base was helpful to you. 
Read more

How to Add/Delete Data or Log Files in MSSQL Server?

In this tutorial, we can check how to add/delete data or log files in MSSQL Server. The MSSQL server is a database server that acts as a relational database management system. The MSSQL server is a software product that helps to store and retrieve user data as required by other products or applications. The SQL server can be either installed on the same system or a system across a network. In this tutorial, you acquire a knowledge of how to add or delete data/log files to a database and also to attach a database file to SQL Server Express. How to Add Data/Log Files to a Database Open the Object Explorer and connect to a SQL Server Database Engine instance. Expand the ‘instance’. Then, Expand the ‘Databases’. Open the database ‘Properties’ by right-clicking the specific database to add the files. This above step opens the ‘Database Properties’ box; in there, select the ‘Files’ page. In the ‘Files’ page, click the ‘Add’ button to add a data or transaction log file. Then, select the file type that you are going to add, such as data or log. If you want to add a data file, you should have a filegroup available. Select the filegroup to which you are going to add the file from the list or select ‘<new filegroup>’ if you want to add the file to a new filegroup. Note that there is no need to put the transaction logs file in the filegroups. Then, specify the initial size of the files. Always make sure that the data file size gets specified in no small value possible, as you can expect a maximum amount of data. Specify how your files should grow by clicking the three-dot icon. This icon opens the ‘Autogrowth’ column. From the column, select any of the following options to specify the growth of the file. Check the ‘Enable Autogrowth’ option, if you want to allow the recently selected file to grow as more space gets required for the data. After enabling Autogrowth, select any of the following options to specify the value. If your file should grow by fixed increments, then select the ‘In Megabytes’ option and specify the value. To specify the growth value in the percentage of the current file, select the ‘In Percent’ option, and specify the percentage value. Next, specify the maximum file size limit by selecting any one of the following options. Select the ‘Restricted File Growth (MB)’ option to specify the value of the maximum file size that should be allowed for the file to grow. If you don’t want to set a maximum value for the file size, then select the ‘Unrestricted File Growth’ option. To completely stop the growth of the file, then clear the ‘Enable Autogrowth’ checkbox. In this case, the file won’t grow beyond the specified ‘Initial size (MB). Next, mention the path of the file. Make sure that the file path is already existing, before adding the file. Click the ‘Ok’ button to add the data or log file. How to Delete Data/Log Files from a Database Open the ‘Object Explorer’ option and connect to the SQL Server Database Engine instance. Expand the selected instance and under that expand the Databases. Under the database, right-click the database from which you want to delete the file to open the Properties window. From the ‘Database Properties’ window, select the ‘Files’ page. Select the file that you need to delete from the Database files grid, and click the ‘Remove’ button. Then, click the ‘Ok’ button to save the changes. How to Attach a Database File The ‘sqlcmd’ tool helps you to attach a database file to an instance of the SQL Server Database Engine. If you want to link a database file available on your old SQL installation to the new installation, then you can attach the database file to the new SQL installation. Also, this process helps you to attach the backed-up copy of your database if it gets corrupted. To attach the file, follow the below process. Log in to the server via RDP and open the command prompt. Run the following sqlcmd command to connect to a SQL Server instance. > sqlcmd -S <Server_name>\<instance> In the above command, replace <Server_name> and <instance> flags with your server name and the name of the instance, respectively. To attach the file type the following command after successfully connecting to the instance. In this command, the databasename flag represents the name of the database that you want to attach. The FILENAME flag represents the path and the file name of logs and database files. USE [master] GO CREATE DATABASE [databasename] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\<databasename>.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\<databasename>.ldf' ) FOR ATTACH ; GO Run the following command to verify if the database is attached. This command asks the sqlcmd tool to display the names of all the databases attached to the connected SQL Server Express instance. Check if the database you have attached in step 3 is available on the list. select name from sys.databases go
Read more
22 Feb, 20

Identify location of the SQL Server Error Log file

Problem How do I find out where the SQL Server Error Log file is located for a specific SQL Server instance? In this tip we look at different ways a DBA can identify the location of the SQL Server Error Log file used by an instance of SQL Server. Solution In this tip we will take a look at three different ways you identify which SQL Server Error Log file is used by an instance of SQL Server. 1. Reading the SQL Server Error Logs2. Using SQL Server Configuration Manager3. Using Windows Application Event Viewer Let’s take a look at each of the above options in detail. Identify SQL Server Error Log File used by SQL Server Database Engine by Reading SQL Server Error Logs The SQL Server Error Log is a great place to find information about what is happening on your database server. You can execute the below TSQL command which uses the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log to find the location of SQL Server Error Log file used by the instance of SQL Server.   USE master GO xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' GO     XP_READERRRORLOG The parameters you can use with XP_READERRRORLOG are mentioned below for your reference: 1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…2. Log file type: 1 or NULL = error log, 2 = SQL Agent log3. Search string 1: String one you want to search for4. Search string 2: String two you want to search for to further refine the results5. Search from start time6. Search to end time7. Sort order for results: N’asc’ = ascending, N’desc’ = descending By default, there are six archived SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. Hence I recommend you read this tip Increase the Number of SQL Server Error Logs. Identify SQL Server Error Log File used by SQL Server Database Engine Using SQL Server Configuration Manager 1. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager 2. In SQL Server Configuration Manager, click SQL Server Services on the left side and then right click on SQL Server (MSSQLSEVER) and select Properties from the drop down as shown below. For a named instance, right click on the appropriate SQL Server (INSTANCENAME) service. 3. In SQL Server (MSSQLSERVER) Properties window click on the Advanced tab and then expand the drop down next to Startup Parameters. The location of SQL Server Error Log file is mentioned next to the “-e” startup parameter as highlighted in the snippet below. Identify SQL Server Error Log file used by SQL Server Database Engine Using Application Event Viewer 1. Click Start -> All Programs -> Administrative Tools -> Server Manager. 2. In Server Manager, expand Diagnostics, expand Event Viewer, expand Windows Logs and then select Application on the left side panel. In the right panel you need to filter for events with Event ID 17111 as shown in the below snippet. To set a filter right click on Application and select Filter Current Log. 3. To view the location of SQL Server Error Log file double click an event and you can see the event properties as shown below.
Read more
22 Feb, 20

Troubleshooting Microsoft SQL Server Error 18456, Login failed for user

Login errors with Microsoft SQL Server (MSSQL) are a fairly common issue and can be easily solved with some basic troubleshooting steps. Before we dig in, let’s take a look at the details of the error to try and determine the cause. Solutions to Microsoft SQL Server Error 18456 Sometimes, the error presents as “login failed for user ‘<username>’,” this information will help us as we identify the user we need to troubleshoot. From the message, we’ll know the error number as a reference to search for next steps. In this case, it is Microsoft SQL Server, Error: 18456. Other times, we may only see “Microsoft SQL Server Error 18456” along with the severity and state number. On its own, a state number might not mean much, yet it can offer more details as to what is wrong and where to look next. These states of the error, 18456, are the most common. The descriptions and potential solutions offer a quick explanation and potential troubleshooting guide.     Step 1:  Log In with Remote Desktop The troubleshooting and solutions require you to login to the server or at least be able to make a Windows Authentication connection to MSSQL using Microsoft SQL Server Management Studio. The most common and easiest method is to connect directly to the server with a Remote Desktop Connection. If you need more information about Remote Desktop Connection, these Knowledge Base articles will help you get connected: Step 2: Run Microsoft SQL Server Management Once you are logged into the server, you’ll want to run Microsoft SQL Server Management Studio (SSMS). SSMS is the tool best suited to configure, manage, and administer MSSQL. When you start SSMS, you will be asked to log in to the server. By default, most MSSQL servers have Windows Authentication enabled, meaning you must log in with the Windows Administrator or the account specified as the SQL Administrator when MSSQL was installed and configured. In addition to Windows Authentication, MSSQL supports SQL Server Authentication. Depending on the version of MSSQL and how it was installed and configured, you may or may not have SQL Server Authentication enabled by default. Step 3: Checking the Server Authentication Mode Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MSSQL is set up to allow both Windows and SQL Authentication. In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Properties. Next, click the Security page. If you find Windows Authentication is the only mode configured, this is the likely cause of Error 18456, Login failed for user ‘<username>’. Setting the Server authentication mode to allow SQL Server and Windows Authentication, you will be able to login to MS-SQL with a SQL user and password or a Windows user and password. After making this change, you will need to restart the SQL Server service. Step 4: Restart the SQL Service In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Restart to apply the new authentication mode settings.   In the above example, Windows Authentication mode was the only mode configured, and the Error 18456 occurred because the user ‘sa’ is a SQL user and SQL Server Authentication was not permitted. Step 5: Checking SQL User Permissions As we check the SQL user permissions, we need to answer the following questions: Is the user allowed to log in? Does the user have a valid password set up? Does the user have the needed permissions for access to the desired database? In SSMS Object Explorer, expand Security, Logins. Locate the user that was failing to log in. A red x on the user indicates this user has login disabled. To allow the user to login, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK. After refreshing the list user logins, we can confirm the user no longer has a red x present. This should allow the user to log in. In this example, the SQL user ‘sa’ failed to log in because there was no permission to log in.  Continuing with user troubleshooting, right-click the user and choose Properties, then click the General page. Here you can enter a new password and then enter the confirmation password. Click OK to save the new password. We set a new password for the user so that we are certain of the password when we attempt to log in. Step 6: Mapping the User to the Database Our last step in troubleshooting a user is to check user mapping to verify the user has access to the desired database and to set or verify their role for the database. Right-click the user and choose Properties, then click the User Mapping page. Select the Database from the list of databases. From the database role memberships, select the desired/required memberships. Click OK. In this example, we mapped the user ‘ProdX709’ to the database Production X709.2019 and granted them database role db_owner. In many cases, you only need a user to have db_datareader and db_datawriter roles to be able to read and write to the database.   In this troubleshooting article, we learned how to identify specifics of Error 18456 to help us track down the root cause of the issue. Still looking for support?  Our MSSQL database solutions come with assistance from our technical support team. Find out how our high-availability database can work for you!
Read more
Cart

No products in the cart.