SQLGear

While creating the build of a SQL server database project you could face following error: Error Deploy72002: Unable to connect to master or target server 'DatabaseName'. You must have a user with the same password in master or target server 'DatabaseName'. This error could be caused by the user account used in publish profile with could have sysadmin privileges. Since sysadmin users on server are mapped with user "dbo" in database hence this error. You can create or use another user with db_owner permission on the database. This could resolve this issue.

What does this error mean: Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'GENERATED'. CREATE TABLE [dbo].[TableName] (....... On your SQL Azure database from where the export is taken, the table for which the error is shown has system versioning turned on. Following is the code to turn off the system versioning on the table: In case if you want to drop the columns associated with versioning you may run following code to first drop the constraint on both the columns and then drop the columns: ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [DF_SysEnd]; ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [DF_SysStart]; ALTER TABLE [dbo].[TableName] DROP COLUMN [SysStartTime]; ALTER TABLE [dbo].[TableName] DROP COLUMN [SysEndTime]; Note: The constraint names and column names could be different in your case.

You can find many articles on the internet for moving to SQL Database but very less on moving to in-premise from SQL Azure . SQL Azure is a good product but sometimes you will need your database to move or copied over to in-premise SQL Server for a task. SQL Azure constantly updating itself and if your in-premise SQL Server in not of the latest version you might face error while trying to import it. This happens when developers are developing on SQL Azure so the code they had written was not tested for compatibility with in-premise. If you start importing the .bacpac file you might face only one error at a time. You fix that in SQL Azure and again start and export and then try importing it and again you face an error with another object. You will face only one error at a time even the database has more than one compatibility issues. So how to know how many such issues are there in SQL Azure database? To find that you have to do this check. Even before you start to export your...

Recently while changing the pricing tier of one of the Azure databases I faced following error: Operation name: Update SQL database Error code: 45197 Message: A system maintenance operation is in progress on server 'servername' and database 'databasename.' Please wait a few minutes before trying again. I tried it again and again but was facing the same message. Ultimately it was resolved by the Microsoft Product Group team efficiently and in due time. There could be different reasons for the issue to occur but this was caused because of following reason as per Microsoft: When a database’s performance tier is changed, the database is moved from a container with the old performance tier to a container with the new performance tier.  The old container is then cleaned up at the end of the process.  In this case, cleaning up the old container was not making progress. It was mitigated by the MS support team by  manually forcing the cleanup to occur. As of no...

Recently a job failed with the following error: Database ‘DatabaseName’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly. Database was in RECOVERY_PENDING State and log_rescue_wait_desc was showing ACTIVE_TRANSACTION. There were disk space issues on both drive hosting the Data files and Log files. Making some space free on both these drives did not help. Alter database with set online command helped. Following is the command which was executed: ALTER Database DatabaseName SET ONLINE;

While trying to attach the .mdf file of a database I faced following error: Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "D:\AdventureWorks2012_Data.mdf". Operating system error 5: "5(Access is denied.)". This was resolved by running the SQL server management studio as Administrator account shown below.

Sometimes you may need to find number of rows in each (or few) tables. Following query will determine the number of rows in each partition of each table in each schema.

One of our developers raised an issue mentioning that he tried to connect to the database but and he seemed database was corrupt\disabled. He mentioned that the day before they did some work on that database which made the database grow considerably. Following is the message he faced: Database 'abc' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945) When I connected to the SQL server instance I found that I was not able to expand the database abc and when I checked the shutdown property of database I found that it was shutdown. Following is the screenshot. I executed following commands to make the database offline. Make sure you do this from the server itself and not from any client machine. USE master; GO ALTER DATABASE abc SET offline; GO Then executed the following command to make the database online again. USE master; GO ALTER DATABASE abc SET online; GO This b...

Credits: Cea. You get a request to create a database for an application and when you ask the requester where do they want their database be placed, they may leave it to you. This often happens. Now with so many SQL Server instances in your environment where would you place it ? The target size of the DB and available free disk space on server are one of the thing to be checked but there are other things too. The one question which would arise is should the database be placed on a SQL instance with other databases, or on a separate SQL instance of its own or in a separate windows host of its own? Following is one of the way to identify where the new database be placed: Application requires a login to be given access to server role on the instance In this case if the database is placed with other databases on a shared SQL instance then there is a thereat that the login which is given access to server has access to other databases too. In this case the database should be placed in i...

I just found that when trying to attach a database in SQL Server 2008 Express edition installed on Windows 7 user would encounter following error. ("CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file...") Try starting the SQL Server Management Studio as Administrator account. Now attach the required database file.

Sp_helpfile is an option to query the size of the database file but it does not give how much disk space out of the allocated is being used by the data or log file. Below is the query which can be used to fetch the details like disk space allocated to each data and log file for the database and how much is used and how much is free. It also gives the physical location of the files. USE DatabaseName; GO SELECT NAME AS LogicalFileName ,CAST(size / 128.0 AS DECIMAL(20, 2)) [Total(MB)] ,CAST(CAST(FILEPROPERTY(NAME, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL(20, 2)) AS [Used(MB)] ,CAST((size - CAST(FILEPROPERTY(NAME, 'SpaceUsed') AS INT)) / 128.0 AS DECIMAL(20, 2)) [Remaining(MB)] ,physical_name AS Path FROM sys.database_files