Can I execute these commands while the database is in
multi_user mode and is being actively accessed (via a
ColdFusion web app)?
dbcc shrinkfile (Web_data2,EMPTYFILE)
GO
alter database Web remove file Web_data2
GO
.
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB
2) Create table called TEST on primary
3) Insert 40MB of data into test
4) Create another file group called temp in primary size 200MB
5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group.
6) Add another 2 files called DATA2 and DATA3. Both are 200MB.
7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3
8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB,
DATA2 = 13MB,
DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB
DATA1 = 10MB
DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over
the remaining files in PRIMARY.
On a SQL Server 7.0 database I support, I've been unsuccessful trying to shrink a data file using dbcc shrinkfile (datafile_logical_name, 0). This worked fine for shrinking the log, but of the 4 datafiles that were created 2 shrank successfully and 2 remain unchanged. Unless the information on the General Tab on Enterprise Manager is incorrect, of the 15000MB allocated for one of the files, only 700 MB are used.
View 1 Replies View RelatedI am running a DBCC SHRINKFILE on "FILE1" of a database (it has fileid = 1)...intent is to remove 70GB of file space:
DBCC SHRINKFILE (N'FILE1' , 400000).
For the SPID that's doing the shrink, In activity monitor you can see:
Waittype: PAGEIOLATCH_EX on resource: 9:3:15411328
(the DB is dbid=9)
But why does it need a page from fileid=3? Are there page dependencies between files that prevent moving a page within a given file? Does it need that fileid3 page to come along?
Its just sitting there in the SUSPENDED state for the last hour....I am going to leave it another 5 hours or so before cancelling.
The dm_exec_requests has an estimated percent complete at 83% and holding....not sure if I can believe that.
I am programming to read read sqlserver log file without shutdowning sqlserver. However it is said that the file is used by another process which I believe is sqlserver.
Is there any way to open log file without shutdowning server? I know that Log Explorer can read online log file. But I do not know the technology they are using.
Hi!
I would first like to thank the person (Ray) who answered my question and also those who read it and tried to find an answer to it.
But, it didn't quite help me much, so I'll ask again; reformulate my question.
Here it goes:
I would like to know if I can make a batch file, that excutes or starts an active script (in enterprise manager go to SQL Server Group/Management/SQL Server Agent/jobs/Dbl-click on a job/then go to the steps tab and you will see that the type of the job is an active script. Or, try to see if you have an active script job).
P.S. It's not a SQL script (*.sql).
What I want to do is to start that job from a batch file.
If I can, can you give me more info on how to do it (command line, parameters), a web site, anything that can help me do that.
If I cannot do that, let me know please.
Thanks a lot!
I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
[code]....
can see how many sessions logged in per hour with the following statement:
SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041
[code]....
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
I have SQL Server Standart Edition and ý have a database, MDF file size is 650 MB, log file size is 1,23 GB
I tried to shrink the log file but i didnt shrink, I wanr to remove all records from the log file
How I remove records from the log file I want to shrink this file to 1 or 2 MB
Please help me
I'm taking the Administration of a DB wich it has on system table sysaltfiles some leftoff files that are not being used anymore on TempDB,
how can i remove them ? Every time i restart the SQL Service it tries to open those files on sysaltfiles..
I tried ALTER DATABASE tempdb remove file XXXX , it did not work...
I got this error:
ALTER DATABASE failed. Some disk names listed in the statement were not found. Check that the names exist and are spelled correctly before rerunning the statement.
Hi
I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.
The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up
"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"
But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.
I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.
Any help will be much appreciated.
Regards
Adnan
I have a SQL Server 2000 database with a primary data file (MDF) and a secondary data file (NDF). I would like to remove the secondary file and only have the MDF. Is this possible?
Thanks, Dave
I have added ndf to tempdb for checking performance improvement.... Now I want to remove the ndf file... I am using below command...
USE tempdb
GO
DBCC SHRINKFILE (3, TRUNCATEONLY);
GO
use master
go
ALTER DATABASE TEMPDB Remove FILE tempdev1
Results:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
23766476643232
Error:-
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note:
=>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error...
=> I executed above commands and restarted services...no change...
How to remove / drop ndf file...
Hello,
Does anyone know of a way to remove the first line of characters from a flat file?
Thank you for your help!
cdun2
Hi
I am newbie in SQL Clustering. I have set up a Windows Server Cluster with 2 nodes and am having the following problem with Physical Disk resource for cluster groups:
My Default Cluster Group (named Cluster Group) has IP Address, Network Name, Physical Disk and MSDTC resources. In addition to that my Default SQL Server instance resources are also in this group. I had this initial set up for Active/Passive mode.
Now I am trying to set up a SQL Cluster in Active/Active mode. For this I have to install another instance of SQL Server in the existing cluster and make a separate cluster group for its resources. I made a new cluster group (SQL Instance Group) with an IP Address and a Network Name resource for this new instance but I dont have any Physical Disk resource to allocate to it. As such while installing the SQL Server Instance I get stuck when I'm asked to select the quorum disk to be used.
Is it possible to configure two quorum disks, one for each group?
What's the concept of dedicated disks resource for each sql instance in a group? Is this same as the quorum disk? If this is not a shared disk how do I configure a dedicated disk resource for my second cluster group (SQL Instance Group)?
Anyone could please help me out with this?
Hi folks,
We are going thru the process of scoping an active/active cluster at one site.
I was wondering whether there will be any issues with mirroring (DB by DB) off the cluster into non clustered server at an alternate DRP site.
Regards, Brian
Sutherland
Hi all, My aim to install active/active 2 node SQL 2005 cluster. I have installed sql cluster on one mode, which automatically installed on node 2.
it works great. However When i tried to install second virtual server, it is not allowing me to install.
it says already install, can anyone tell me how to install active active cluster
I'm attempting to alter a database by removing one of two log files. I have truncated both log files successfully and the database has no processes but I get an error stating file cannot be removed because it is not empty. Help
View 2 Replies View RelatedIn tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. Â I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
I have also tried using the command:
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev#, FILENAME = '<new location');
Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.
I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.
I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.
Log file rotation and cleanup script and how to remove the log file older then x days.
View 7 Replies View Relatedhi
I have a flat file as below
Date 20070606
Empid Salary X1 x2 x3 x4 x5
100 10000 .............................
where 10000 is the salary got on date 20070606 by empid 100
.x1,x2,x3...are the remaining columns in the file.I need to extract the date and also continue reading the remaining data.How can i do this???
Any help?
thanks
ami
Hi again!
I would like to thank the 2 persons which I had an answer from my previous question first. Thanks a lot !
But, it didn't quite answer my question so I'll give a bit more info.
I would like to know if I can make a batch file, that excutes or starts an active script in SQL Server Agent (that is in SQL Enterprise Manager).
If I can, can you give me more info on how to do it (command line, parameters), a web site, anything that can help me to that.
If I cannot do that, let me know please.
Thanks a lot!
We are using log shipping and we would like to remove all transfered and applied journal (in the primary box).
We have the intentionto use a trigger like this :
CREATE TRIGGER del_log
ON log_shipping_plan_history
AFTER INSERT
as
declare
@lastfile nvarchar(256)
SELECT @lastfile=i.last_file
FROM log_shipping_plan_history e INNER JOIN inserted i ON e.sequence_id = i.sequence_id
where i.activity=1
begin
if IF (@lastfile <> NULL)
...
... remove file (using xp_cmd for example...)
...
end
but the problem is that we have only the last file transfered and applied that will be removed
(some time, more that 1 file are applied in one shot ...
see num_files column in log_shipping_plan_history).
Any solution to remove all the files generated before the last one given by the query ?
Any other solutions (sql wizard gives the possiblity to to remove file after a laps of time 1hour, 1day...).
I am looking for the table that contains all the journal files (that we can see when we try to retore a db) ?
Thanks
We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:
Error: the file PRADATA4 cannot be removed because it is
not empty.
The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:
1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)
2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO
Can anyone help?
Thanks
I have a CSV file that I am importing via SSIS into a SQL table.On the Flat File connector, I have specified Line Feed as the row delimiter.The data flow is failing due to some of the rows having line feeds before the end of the row.Is there a way to get rid of some line feeds but not others, so that I can run the data flow successfully.
View 6 Replies View RelatedHi,
We are trying to set up an active/active configuration of a SQL Server
cluster, and we had a few questions.
Initially, we want to have 2 Database Servers that would share the same
Database (both reading/writing to the same tables).
However, from reading the MS docs, we find out that we can have what
they call an "active/active" configuration using a Cluster but they
need to have 2 different disk sets, i.e. having 2 separate databases.
If this assumption is correct, how does the data get synchronised
between the 2 databases (that are on the 2 different disks sets)?
Can anyone help? thanks
Axel
ACTIVE/ACTIVE SQL Server 2000 Cluster
This was my plan.
Physical box - box1,box2
virtual box - virtual1
i have a active/passive cluster virtual1 (primary node box1, secondary box2)
I am creating another virtual box on box2 as primary node by name virtual2)
whenever my installation setup runs my default server name is greyed out. it always requires a instance name.
Is that the way it should work?
i can only use instance for active/active failover?
please advice
How do I install MSDTC in an active/active MSCS cluster environment?
If I run comclust.exe, I can only activate MSDTC on one of the nodes at a time.
I need DTC on both nodes.
(Opsys is W2K with SQL 2000)
//Mange :)
Hello folks,
have you ever heard of an Active/Active SQL Sever 2000 Cluster deployed in a pure OLTP environment?
Some 8 years ago I have learned about a bespoke solution for the SAP ERP system (not the BW!) with DB2 Parallel Server for a huge German company. Then again, I would expect that Oracle RAC might fit into an OLTP environment, although I never heard about a real world implementation.
All this led me to believe clustering is good for failover purposes, and for decision support services, not quite for OLTP applications.
So if you see a point in Active/Active Clustering and OLTP please come forward and explain.
Cheers,
Johann
P.S: For those of you who want to know: Consultants from www.hiltes.com want us to deploy an Active/Active Cluster for their Fashion 3000 Net stock software.
We run several SQL 2000 SP4 instances on IA64 active/active cluster. The OS we run with is Windows Server 2003 SP1. We have different network cards : 2 network cards teamed for production purposes in domain X and 1 network card dedicated for problem solving in domain Y.
First we configured the cluster with only the 2 teamed network cards for production purposes in domain X. Later we introduced the second network card for problem solving in domain Y.
Everything looks fine. The (virtual) SQL instance listens on two different IP adresses on TCP port 1034. If we try to make connection via isql, EM or Query analyzer than we can directly contact the SQL instance from a workstation/server within domain X but this doesn't work form a workstation/server within domain Y. However if we use the specific TCP port in the connection in domain Y the connection is setup. We wish however not to use explicit TCP ports in setting up connections.
Has anyone experienced the same problem before or has anyone an idea how to solve the problem?
I have a 2005 active/active cluster and want to apply SQL server 2005 SP2 to both node.
I know that for active/passive , the sp can simple be installed on the active node(instance) and everything will be replicated to the other node.
bear in mind what I have SSIS and SSAS and SSRS running on the active/active cluster.
what is the rigth method for applying the SP to activeactive cluster?
Thanks
In DTS/SSIS packages, How do to data validation or check for special characters and remove them at Copy column level.
thanks,
Hi all,
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
Thanks in advance,
Scott Chang
Hi
While configuring an active / active cluster, do I need to run the SQL setup on both nodes?
The SQL2K setup installs binaries on localdisks of both nodes - hence the question.
thanks
Liju