Create a table with an Identity column, insert data / restart the server / insert more data / restart the server/ insert some more data.
My data looks like this :
Identity column
1
2
3
1002
1003
1004
1005
2002
2004
It looks like the indentity value gain +~1000 after most server restart (sometimes identity stay the same). This can be very dangerous for some datatype! The only thing google told me was this : URL...but microsoft did not comment on it yet!
We just switched from Sql server 2008R2 to Sql server 2012.I am facing one problem with identity Columns "When ever i restarts my sql server,the seed value for each identity column is increased by 1000 (For int identity column it is 1000 and for big int it is 10000).
"For Example if seed value of any table was 3 then after restarting sql server will be 1003 if i again restart sql server it will be 2003 and so on."
After searching on google i found that it is a new feature (don't know what is use of it) in sql server 2012 and having only two solution if you want old identity concept
1. Use sequence object -
a) I am using same database in sql server 2008 and 2012 both so can't use sequence in 2008.
b) if i go with sequence then need not change save procedure for each table,which is bulky task for us.
2. Use Trace Flag 272 (-T272)
I can go with this solution because there is need not do any changes in my application.Some one suggested me that add -T272 in startup parameter,after this sql server identity column will work normal as previous version.I did the same but it is not working.
I don't want to do any changes in my database structure.
how to use this -T272 or why it is not working.
I don't want to use this new identity feature how to suppress it. Why -T272 is not working.
I have a system log with NULL gaps between a sequence of numbers...see "BEFORE" sample below.
The number of gaps between the Sequence_ID's are arbitrary, but generally less then 50 records.
I'd like enumerate the gaps to produce the "AFTER" result, but do it with a single query or view, not through procedures.
I've been playing with windowed functions and groupings with no success. I'm guessing it'll need some recursive CTE logic, but I haven't been able to figure it out the correct loop.
See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year.
SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL
[Code] .....
-- expected result
SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL
I setup Sql agent for autostart with sql server and autorestart if stoped but after rebooting NT or after stoping SQL server it never starts ,I have to start it manualy
I want to schedule a sql job to run at specific time for restarting the sql services(MSSQLServer and SQLServerAgent) and also want to send notification to all the users 5 minutes before restarting them. Please let me know. Thanks in advance.
I need to restart (becouse of the Database Mail :( ) SQL Server Agent on a live server which acts as a distributor for a lot of replications. I know that it shouldn't cause any problem, but I want to confirm that it want couse a subscriptions to be reinitiated.
We have an ASP application installed on one powerful Win 2003 andthe SQL Server on another Win 2003.We have two copies of the application (identical, one for productionand one for test purposes) that are operational. The 2nd applicationis connected to the same SQL Server but a test copy of the productiondatabase.Today, running the application, a place where it shows a list of records,the ASP application was timing out after 30 seconds for a lot of users.I backed up the production db and restored it over the test db and testedthe ASP application; within 10-15 seconds the ASP page loaded the listof the records, so i didn't get any timeout. So i started to think thatmaybethe test db when it was being restored, SQL Server would do some kind ofdata cleanup and defragment the new db.But, then i checked the Task Manager, SQL Server was almost using 1.2GBof memory and the server had only 100megs available.As soon as i restarted the SQL Server, the ASP page did no longer timeouton the production database.So now i am trying to figure out why the SQL Server restart fixed myproblem?Unfortunately i did not check if there were a lot of connections in theProcess infounder the Current Activity in SQL EM.Do you think there were a lot of sessions that weren't killed by theapplication?And that was causing the timeout? But why only for the Production db and notalsofor the test db?I mean, i am puzzled as to why before restarting the SQL Server, running theASPapplication connected to the TEST database did not time out!As always, I appreciate any feedback, comments.Thank you
I have the default trace on a SQL Server 2008R2 instance enabled and found today that there is a gap of nearly 4 minutes in the trace during a time of the day when there most certainly is not going to be a 4 minute window of nothing.
What if anything could cause the default trace to have a gap like this? The SQL Server Instance (against my preferences) is hosted on VMware however it has its on HOST and so its resources are not being shared with any other server. The data & log files reside on different parts of the SANS. Our IT & Network admins are looking into the issue on their end but when I looked and found a near 4 minute gap in the default trace it hit me that this could be something above/outside of SQL Server.
I have a problem where developers will stop the SQLServer service during the day and then they will not remember to restart the SQLAgent. It does not seem to restart when you stop the Server service and restart the services with out a reboot. Is there a setting somewhere in Enterprise Manager where I can tell the Agent service to restart with the Service service?
i have a question regarding sql agent. Is it ok to restart the agent any time (i need to restart it for enabling the email alert option in sql agent properties..otherwise it doesnt work)? Does restarting sql agent affect replication jobs and other scheduled jobs? Are there any issues restarting agent on production?
If SQL is restarted for any reason, I need SQL server to send emails to others letting them know that the server is has restarted once it comes back up.
Is there an Error # that I can build an Alert on? Ideas???
Why does restarting my server improve SQL's performance so dramatically? What can I do to achieve the same effect, without restarting? Thanks for any ideas.
I am running SQL Server Enterprise manager. I was able to connected to the database properly. But after restarting the PC I am getting the following error.
"System.NullReferenceException: Object reference not set to an instance of an object"
The situation is as follows: Database A is being replicated to database B on a networked server. The application vendor is in the process of doing customizations which require frequent table changes. So the vendor (who has access to the replicated DB) stops replication in order to upgrade some tables. But I am finding that either he has forgotten to restart replication or, more than likely, has broken it.
So, if anyone is able to comment on a process which will allow changes to database tables and the restoration of replication, I would be very grateful. thanks in advance.
If you create a #temp table on stored procedure #1 and then call another stored procedure #2 from #1; if stored procedure #2 has an error in it, the #temp table will not release even though stored procedure #1 has a drop table statement in it.
I am new to SQL Server. I've created a database with a key ID field that is set to automatically increment. Well, after adding records I've got some gaps in my numbering and want to renumber from 1 to eof.
What is the best way to do this in SQL Server 2005?
In a cursor, I declare a table variable like so: DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int) I then insert into that table from another table. The purpose is to get a list that looks like this after the insert: RowID valueID1 348972 345223 94822 etc.... However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1. I tried delete from @TempTable DBCC CHECKIDENT(@TempTable , RESEED, 0) but I get 'Must declare the variable @TempTable table' error. Is there a way to destroy and recreate that @TempTable variable?
I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005
I had moved the master db under the minimal configuration. using the parameters /f and /T3608
I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005
I had moved the master db under the minimal configuration. using the parameters /f and /T3608
I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005
I had moved the master db under the minimal configuration. using the parameters /f and /T3608
Hi,I've got a full text index which works fine, SQLSERVER2000/WIN 2000 SERVER.The system requires to update indexes immediately, so I use a timestampfield to enable this. No problems so far.Now, I've got a stored procedures which nearly daily inserts about 10.000rows. When doing this while full text indexing is active, all users startcomplaining about performance. In order to work around this problem I trieddoing the following ...Create myStoredProcedure-- begin of stored procedureexec sp_fulltext_table 'adsfull', 'stop_background_updateindex'exec sp_fulltext_table 'adsfull', 'stop_change_tracking'---- insert 10.000 rows---- end of stored procedureexec sp_fulltext_table 'adsfull', 'start_change_tracking'exec sp_fulltext_table 'adsfull', 'start_background_updateindex'Now, it seems this doesn't work. SQL Server keeps tracking changes andupdating indexes. Also if I cut away the stop instructions and paste theminto query analyzer before starting the stored procedure.So, if I check the status via select fulltextcatalogproperty('FTADS','Populatestatus') ... it returns value 6 (incremental in progress) insteadof 0 (idle) while executing(0=idle, 1=full population in progress, 6=incremental in progress, 9=changetracking)The only way I can resolve this issue is to stop the indexing via theenterprise manager and to restart after the stored procedure is executed.Any help appreciated.--Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
This result represents the times in minutes that are available.
I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @reservations table.
I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)
I have a table with an identity column..How will the identity gaps be adjusted if i delete few records in the table..ie..the sequence should automatically adjusted..Is there any way for this ?
ID Name City 1 abc xyz 2 mexm mcel 3 olekc kcome
Suppose i delete the record where ID=2..still the sequence should be auto adjusted..ie.the record of ID=3 should become ID=2 automatically..there shouldn't be any gaps.
I have a row in my report that has 6 columns. I want to outline the row with a border, but not the column lines in between. So, I went into BorderColor, changed the Default to white and my Top and Bottom colors as Black. The problem is that where the "white" column lines are, they are displaying 1pt gaps in my outline. I tried changing the BorderStyle to Zero, but it wouldn't take it.
When I try to export my new report to PDF, I get extra blank lines in my list(s). My report has a list, with another list inside of it (among other textboxes). When I generate the report, or print it to a printer, it prints as expected. If I export to PDF, it adds in a bunch of blank lines into some of the inner lists. It doesn't seem to reflect any issue with the data, but it is consistent where it puts the spaces. Anyone know of any settings I might be missing?
We are running SQL Server 2014 Web Edition (64-bit). On three occasions now we have had two of our live secondary log shipping databases become unusable through the log shipping process. We ship logs to these databases every five minutes, and generally the process works fine. However, on three occasions in as many months we have suddenly received alerts warning us that the transaction log restores could not be performed on the secondary databases. The SQL Server Error Log revealed the following:
Date,Source,Severity,Message 10/13/2015 04:01:16,spid52,Unknown,Setting database option SINGLE_USER to ON for database 'ObfuscatedName'. 10/13/2015 04:01:17,spid52,Unknown,Starting up database 'ObfuscatedName'. 10/13/2015 04:01:17,spid52,Unknown,Recovery is writing a checkpoint in database 'ObfuscatedName' (8). This is an informational message only.
[code].....
An error occurred during recovery, preventing the database 'ObfuscatedName' (8:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support."
The extract above shows two previously successful restores to the secondary database. Suddenly, at 04:11:16, the database can no longer be started up or restored to. This is despite CHECKDB giving a clean bill of health only five minutes earlier. The result is that the last three lines are then repeated ad infinitum.
The only way we have managed to get around this issue so far is to replace the secondary database with a recent backup of the primary. Once this is in place, any outstanding transaction logs are successfully applied when the log shipping restore job next runs. However, this seems rather drastic.It is rather concerning that our warm-standby databases continue to become corrupted without any apparent explanation from SQL Server.
Upon restarting user defined function took seemingly forever to run
I am learning about nested while loops being used in some interdependent user defined functions. They seemed to work OK for a while.
Later, remembering how I lost the database due to hard disk reformatting, I backed up the database and copied it to a rewritable CD.
As the data is not really significant I deleted the database and practiced restoring the database from the CD.
This morning I restarted the user defined function and ran it. After more than half an hour with no result I gave up. Normally it took much less time to run such a user defined function.
I re-ran some other UDF and they worked. However, after I made some minor amendments to the TSQL scripts, saved the UDF and re-ran it, and it again seemed to take forever to run, even when I had set the counter in to while loop to 2.
I don€™t know what had gone wrong.
I went to register my copy of SQL 2005 EXPRESS. It didn€™t seem to help.
