Tag Info

New answers tagged

2

You need to include the .NDF file in your restore script. RESTORE DATABASE mydb FROM DISK='\\150.10.10.12\backup\mydb.bak' WITH REPLACE , RECOVERY , MOVE 'IMAGENES_Data' TO 'F:\MSSQL\Data\Mydb.mdf' , MOVE 'IMAGENES' TO 'F:\MSSQL\Data\Mydb.ndf' , MOVE 'IMAGENES_Log' TO 'G:\MSSQL\LOG\Mydb_LOG.ldf';


1

The easiest way is to look into the transaction log and see where there is a sequence that you can 'split'. Start by reading How to read and interpret the SQL Server log to understand what I'm talking about. Then look into your DB log for a sequence like this: lsn1 BEGIN XACT xid1 lsn2 some update lsn3 COMMIT xid1 lsn4 BEGIN XACT xid2 lsn5 some ...


2

DTSInstaller.exe consumes a file called with an extension of SSISDeploymentManifest The contents of it look like <?xml version="1.0"?> <DTSDeploymentManifest GeneratedBy="HOME\administrator" GeneratedFromProjectName="SSISHackAndSlash" GeneratedDate="2010-04-30T21:19:08.6336677-05:00" AllowConfigurationChanges="true"> ...


-1

It is a known issue with Sql Server 2008. Happens also in SSAS. Search for Microsoft fix/ or ask support for it (fix applies on the OS site and not the DB site).


0

The only way I can think of doing this is to slightly hack the process. It would involve the following steps: Copy the Distribution agent job and rename it to indicate it is run manually. You'll also need to change/disable the schedule and remove the "-Continuous" flag from the exec command of this new job. Create a job that does the following: Step 1: ...


0

I use your code and added some checks, regarding the COST_CHANGE = 0 For Sql Server 2012, you can use the LAG (this is not available in 2008,2008R2) --my source table DECLARE @tPROD_COST TABLE (ID INT /*IDENTITY(1,1)*/, Duration INT, Cost FLOAT, COST_NEXT FLOAT, COST_CHANGE FLOAT) INSERT INTO @tPROD_COST(ID,Duration,Cost,COST_NEXT,COST_CHANGE) ...


1

Refer to the link Aaron Bertrand provided. There are a limited set of operations that are minimally logged, but reorg/rebuild aka defragmenting are not included. If you wish to minimize the effect, you'd have to do incremental reorgs as mentioned by Cibi or just allocate the needed space if this is expected to occur regularly. I've had to do this before in ...


4

It is very much normal for there to be gaps in identity values (caused by rolled-back transactions, over estimated pre-allocations, and so forth) as you are already aware, though that sort of sparse use of values is rather unusual and indicates that there is something going on which you need to be aware of (either a design issue that may be unavoidable and ...


1

It is in fact a configuration issue in the Oracle Client Set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and no more conversion error. UPDATE Why? Because 0.6 in French would be read as a character value whereas in English it remains numeric. 0,6 in French is 0.6 in English. Defining NLS_LANG as American means that 0.6 stays numeric thus allowing the ...


0

You can script this whole process out with TSQL and SQL Agent jobs depending on the your cleanup process -- I assume you can or already have it scripted but here are the basics how I've dealt with similar issues in environments I maintain and support. Switch recovery model to simple on primary DB (TSQL with SQL Agent job step) -- this will break the log ...


1

Just a quick thought on something to look into, in a domain type environment, some operating systems allow you to logon to the server before full network connectivity is established. You may want to check for either local or domain level group policy settings to not allow logon or OS startup until full network connectivity is established. Just in case ...


1

Converting my comment as answer (SQLFiddle): ;with cte (cityID, label, rowNum, stateAbbrev, stateName) as ( SELECT TOP 100 City.id AS cityID, City.name + ', ' + [State].[State] as label, row_number() over (partition by cast(City.name + ', ' + [State].[State] as varchar(max)) order by City.id desc) AS rowNum, -- if you want the highest ...


1

Another approach that seems not to have been mentioned yet is to have Hazards and Tasks use the same ID space. If a Hazard has a Task, it will have the same ID. If a Task is for a Hazard it will have the same ID. You would use a sequence rather than identity columns to populate these IDs. Queries on this type of data model would use (full) outer joins to ...


2

You can clear the log during the REORG process as its not done as a single transaction. The log bloating depends on index fragmentation level and amount of work done. Again, REORG is a fully logged operation on all recovery models. https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


3

This is what I use to monitor sleeping SPIDs causing blocking issues: SELECT ,s.session_id ,s.status ,s.login_time ,s.host_name ,s.program_name ,s.host_process_id ,s.original_login_name ,s.last_request_end_time ,CAST(t.text AS nvarchar(4000)) AS [text] FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS ...


3

Are there exceptions to the above clustered index rules? The general guidance for selecting a clustered index is good, but there are sometimes additional considerations to be taken into account. Occasionally, these extra factors can be more important than the general 'rules'. Your scenario is somewhat "special" in that you have a very wide table, with a ...


2

Is there a way to get updates automatically? SQL Server 2008 R2 is not supported on Windows 10. This means the SQL Server team will not be releasing any security updates or patches if they find any specific issues related to SQL Server 2008 R2 code. There is no way to get updates automatically as none would be released related to Windows 10. ...


0

Not all updates to SQL Server are available via Windows Update. Some service packs are, some aren't. Check if you have enabled updating other Microsoft products along with Windows. Cumulative updates are not available from Windows Update so you have to install them manually. The installation order is: All service packs in the order of release (SP1, then ...


1

The query linked to, and copied by, @Nelson is inaccurate: it ignores Indexed Views, Full Text Indexes, XML Indexes, etc. If you want a query that will include everything without executing sp_spaceused via sp_MSForEachTable, then I have already posted two variations of it (one here on DBA.StackExchange and the other on StackOverflow) so I won't copy them ...


3

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).


3

It's been answered on Stack Overflow: SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = ...


0

I've found that this ONLY works if you supply a numeric value as the first parameter of RAISERROR. for example: This returns a %errorlevel% = 1: RAISERROR(50002, 10,127) But this returns %errorlevel% = 0: RAISERROR('myErrMsg.', 10,127)


0

Had a similar issue: changed the hostname of a computer where SQL Server and SQL Server Agent is running. Jobs were assigned to . After creating a temp user / logon to SSMS using this new temp user / drop and create the login name (public and sysadmin privs!) / re-assign the jobs to this re-created Login everything was fine. Maybe you could manipulate a ...


7

You could go with your own idea of an asymmetric schema by removing one of the foreign keys from the current set-up, or, to keep things symmetric, you could remove both foreign keys and introduce a junction table with a unique constraint on each reference. So, it would be like this: CREATE TABLE dbo.Hazard ( HazardId int IDENTITY(1,1) NOT NULL CONSTRAINT ...


2

To sum up: Hazards have one or zero Tasks Tasks have one or zero Hazards If the Task and Hazard tables are used for something else (i.e. tasks and/or hazards have other data associated, and the model you showed to us is simplified to show only the relevant fields) I'd say that your solution is correct. Otherwise, if Tasks and Hazards only exist to be ...


2

If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days. As suggested by Kin, have a look at Nacho's ...


11

You need to give the database a valid owner. Use the ALTER AUTHORIZATION command to do this: ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [A Suitable Login]; Related Knowledge Base article Note the sp_changedbowner system stored procedure has been deprecated in favour of ALTER AUTHORIZATION.


2

I realize this has been answered before on Stack Overflow. It got me going in the right direction. I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering. For anyone in the future who has a similar issue: My ...


1

You have to use dbname.schema.objectName e.g. dbname.dbo.MYTABLE in your tsql EXEC msdb.dbo.sp_send_dbmail @recipients='MYMAIL', @body='100 Daily documents downloaded', @subject ='100 Daily documents downloaded from user', @query = 'SELECT USERID FROM dbName.schema.MYTABLE WHERE MYCOUNTER > ''100''' EDIT: You only want ...


1

Just to update, we are going to end up moving some of the load to a different server, followed by a complete rebuild of the environment. Thank you for your time and responses.


0

i test the sp_help_revlogin but it does not worked in SQL 2014 SP1 CU1(12.0.4416) the KB also mentioned that it work till SQL 2012 https://support.microsoft.com/en-us/kb/918992?wa=wsignin1.0


0

I think I would first outline something like this before testing it while recording time with a stopwatch. D-X get the newProd and newMirror ready (OS + SQL Server + User Account, job, ...) build scripts for every step (back, restore, mirror, ...) D-3 add a new IP to oldProd update DNS with new IP and TTL = 300s (Time To Live) D, H-2 or H-1 (I assume ...


1

I worked with a very similar situation like this in the past in a 24x7 manufacturing company and finally decided to use transactional replication. It is possible to configure DDL to be replicated such that you can push out whatever the patches change to the subscriber. Obviously there are pros and cons to everything and you need to weigh them to determine ...


3

On a case insensitive server, those two names are identical. I am not sure if you are using the UI for this, but I get the same error when I run the following code: USE msdb; GO EXEC dbo.sp_add_operator @name = N'NIT and Arts'; GO EXEC dbo.sp_update_operator @name = N'NIT and Arts', @new_name = N'NIT and ARTS'; Yields: Msg 14261, Level 16, State 1, ...


8

Create the new table Drop the old table and rename the new one in a transaction: BEGIN TRANSACTION; DROP TABLE dbo.OldTable; EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT'; COMMIT TRANSACTION; If you have concerns about how long the drop itself will take, you can do it this way instead: BEGIN TRANSACTION; EXEC sys.sp_rename ...


3

How can I check whether the process is actually doing something? For the session 65, process is suspended because its waiting on a process to complete. That wait info itself is highlighted there as PAGEIOLATCH_SH.. This means from MSDN Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in ...


1

Please refer following link to get answer to your question : To get the size of each open transactions refer following link https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/ If you want to know the transaction log of any historical date then you can take the backup of database or just a transaction log ...


0

As in the other answer: you need a recent CU for TLS1.2. See https://support.microsoft.com/en-us/kb/3052404: Cumulative Update 8 for SQL Server 2014 Cumulative Update 1 for SQL Server 2014 SP1 Cumulative Update 6 for SQL Server 2012 SP2 Cumulative Update 7 for SQL Server 2014 After enabling only TLS 1.2 you will possibly encounter two errors: SQL ...


1

As in the other answers: you need a recent CU for TLS1.2. See https://support.microsoft.com/en-us/kb/3052404: Cumulative Update 8 for SQL Server 2014 Cumulative Update 1 for SQL Server 2014 SP1 Cumulative Update 6 for SQL Server 2012 SP2 Cumulative Update 7 for SQL Server 2014 After enabling only TLS 1.2 you will possibly encounter two errors: SQL ...


2

I agree with the above. I would also check your queries hitting the CPU and determine if any query tuning could be implemented or at the very least some plan guides. But all in all, your configuration is just not sustainable.


7

Things I see wrong right away Way to many instances Not enough vCPUs vNUMA probably doesn't match NUMA I'm guessing there's a ton of missing indexes MAXDOP of 1 or 2 Cost threshold of 50 Optomize for adhoc workloads = on (it's probably off) Questions What do the waits look like? What are all the memory configs setup for? Fixing this is going to take ...


10

Yes. Details here: Backup Overview (SQL Server) This is the section relevant for you: Full backup A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data. You should always test the backup (ideally by performing a restore to another location) to ensure you can ...


1

Use something like SSDT to do a schema comparison on the before and after. Check this out: https://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx Note: This is only for the logins, obviously showing passwords isn't supported


0

@PaulBarbin's answer is great if you know that you will always need to output three columns of data. If you need to dynamically output a different number of columns based on the data being selected then you will have to adapt his solution using dynamic sql. Effectively you are trying to get SqlServer to output data contrary to what its natural inclination ...


3

You could try Pivot: I think you'll need to order on some key that ties these records together (how do you know that the starttime of "TESR" is related to the EndTime of "TESR". That probably needs to be in the ORDER BY clause below. SELECT Title ,[1] ,[2] ,[3] FROM ( SELECT Title, Value, ...


3

By all means put it into a standardised set of tables so that you can tweak the import stage rather than having to change complex report(s) and queries. But the data should still be normalised which will require multiples tables (but with good indexes). As others have mentioned, don't use triggers, sync in batches. Don't worry about lots of joins, when ...


9

A query is not an executable program. The SQL query specification language enables us to define the logical results of a query, but it is up to components like the query optimizer and execution engine to take care of the physical processing involved in delivering those results. In principle, the physical processing side of things is free to perform whatever ...


4

Locks will be taken in the same way in both cases, and that will depend on the isolation level and query hints. It will not do anything "clever and limited." It will do what it must to honour the semantics of those settings, in a memory-efficient way. If the two queries produce identical plans, as seems likely in this case, the same objects (row, page etc.) ...


2

My plan was to write all of these records to one "catch-all" table, and write triggers on the original tables to maintain the records in this aggregate table. Triggers have so many problems you should avoid them: An error in a trigger can cause the original transaction to abort Triggers that correctly handle multi-row operations are hard to write ...


7

If I understood you correctly, you have a large third-party system, you don't have much control over it, you make complex reports that read data directly from this third-party database, your queries depend on the internal structure of the third-party database. I would approach it like this: Set up my own separate database, which I have full control ...



Top 50 recent answers are included