Transact SQL :: Check For Mount Points And Retrieve Available Space
Jun 8, 2015I have to check for existence of mount points on drives and retrieve the Space information for those mount points. Is this even possible with t-sql??
View 2 Replies
ADVERTISEMENT
Mount Points
Nov 23, 2006I know how to create mount point in windows 20003 cluster, I am not sure how
to set it up with SQL 2005 running on the cluster.
Does sql need to be dependant on any of the disks? I have tried looking for
a guide, but cannot find.
current setup active active cluster running. I need to add san space which
will hold the databases. The san will be carved up into drive letters. each
drive letter will hold 3 mount points.
ie.
node 1
J:-2 mount point
k:2 mount point
l:2 mount point
node 2-
r:-2 mount point
s:2 mount point
t:2 mount point
each node would be able to own the disk if the other node failed over.
any help is appreciate. I have tried books online etc.. cannot find a good
step by step.;
thanks
Scalability With Mount Points
Feb 28, 2008I understand mount points help scalability in easier maintenance.
By scalability do we mean more than 26 drive letters or it means adding more space to the same mount point letter on with more ease .
Can I add more space to a mountpoint if required later on by adding hard disks .
Also if one can give some pointers to good file group configuration guidelines / storage align partitions , it will be very much helpful
Further I my server CPU has 4 cores , will having 4 filegroups help me in improving system performance.
If SAN has 2 controllers , is it preferred to run data file partition on one controller and log file partition on another.
Thanks in advance.
Monitoring Mount Points
Sep 25, 2007Hello,
We have a requirement to be able to monitor mount points. The xp_fixeddrives does not support mount point monitoring. Is there another way to do it. Does microsoft working on updating the xp_fixeddrives. Let me know if anyone has any ideas how to monitor mount points.
Thanks
Is It Safe To Use Volume Mount Points With SQL 2005?
Jul 20, 2006Hello,
To implement the new SQL 2005, I plan to make the environment easy to manage. The environment should be simple to document and be automated via scripts. Therefore I plan to use mount points as described below.
On a typical SQL server with multiple drives like C, D, E, F, G, H. Where each drive will have various folders to hold SQL code, data files, transaction log files, tempdb files, snapshot files, and other types of files. This typical environment is not pretty and is hard to write scripts for.
So I plan to standardize on one standard directory structure via volume mount point. On all new SQL 2005 servers, we should see drive E as the one and only SQL Server directory. Other drives will be mounted to drive E as shown.
E:
SQLSERVER local folder -sql code for each db instance
SQLSHARED local folder -sql shared tools for all db instances
SQLTLOG1 Drive H -db transaction log
SQLSNAP1 Drive F -db snapshot files
SQLTEMPDB1 Drive H -tempdb main data file
SQLWORK Drive D - DBA work area
SQLDATA1 Drive G -db data files
SQLDATA2 Future Drive -if SQLDATA1 is too large for any direct attached drive, or to get more I/O throughput.
With this implementation, I can easily write scripts to manage the environment. Also if any mounted volume is out of space, we can swap the based drive without doing any change to database configuration. We can also switch from direct attached drive to SAN in the future.
Do you think mount point is safe to use with SQL 2005? I know it is supported.
Do you have a standard directory structure for your environment? How do you do it?
Thanks,
KTMD
SQL 2012 :: Does Mount Points Support Filestream Feature
Apr 9, 2014Does sql server 2012 mount points support FileStream feature?
View 1 Replies View RelatedT-SQL (SS2K8) :: Retrieve Points From Table Based On The Fields When Mapped
Dec 1, 2014I need to retrieve the pts from table #test1 based on the fields when mapped..
Name & Type columns should match ..Based on the data below the output should be
id NameTypeCode CityIType BPS
1EGFN432 HY F2 10
2 EG FN 432 ON F1 20
3 EG FN 433 On F1 30
It has order of priority to get points
1.When all of the field matches then get the respective pts .
2.When name and Type matched and rest fields doesnt match but is null in #test2 table then retrieve those pts
--drop table #test
--drop table #test1
Create table #test
(
id int identity(1,1) not null,
Name varchar(100) NULL,
[Code] .....
Transaction Logs And Check Points
Aug 14, 2001Previously I have posted about my problem with transaction logs. Threw my research I have found the truncate log at check point. I have also been lead to think that a check point could be a full back up. I have set this option to true and then performed a full back up. Afterward I have shrank the database to find the transaction log to still be over 43 gigs. Where have I gone astray?
Regards
SeTi_Ni
Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage
Nov 26, 2015I am using the below script to get space alerts and now i am interested in sending alerts if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Check Space Left
Sep 13, 2007Hi,
I understand that the Express Edition can create databases up to a size of 4GB. Is there a way to check the space left for a particular database via C++?
Thanks in advance.
Regards
Melvin
Check All Databases For File Space Used
Aug 29, 2006Hi There
As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.
However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.
So i want to write a job that checks the percentage space used for all databases.
My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @DBNAME' and execute it the cursor context stays local you do not actually change database context.
So how do i loop though databases, i have found
sp_msforeachdb, however this is undocumented in BOL.
Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.
Obviously i would rather use documented methods.
So bottom line what tsql could i use to check the percentage of file space use for all databases?
Thanx
How To Check Amount Of Disk Space Left?
Jul 16, 2004Here is one of the MCDBA exam question.
Does anyone explain how to do the following?
I click new alert, and selected 'Sql server performance condition alert' Type
But I don't know which object and counter I should choose to get disk space left.
Thank you..
Q: You are the administrator of Microsoft Windows 2000 Advanced Server computer. The server is also
running SQL Server 2000 and contains a database named Sales. The server is configured as shown in
the exhibit.
The sales database is configured by using the default options and increases by 50MB per day. You
want an operator to be notified automatically before the database runs out of disk space. You also
want to minimize the amount of administrative overhead necessary for this database.
What should you do?
A:
Configure systems monitor to log an alert if the hard disk database contains less than 600MB of disk
space. Configure SQL server agent to send an e-mail message notifying the operator of the system
monitor alert.
Explanation: The solution with least administrative effort is to set up a system monitor alert to log when
disk space is below a certain threshold, here 600MB. Then configure the SQL Server agent to trap this event
and send an e-mail notifying the operator when this occurs.
Note: Using SQL Server Agent, you can define alerts and configure responses to alerts.
SQL Server Agent monitors the Windows application log and compares each SQL Server event logged with
the alerts that have been defined. If a match is found, an event alert fires. SQL Server Agent can also
monitor specific SQL Server performance object counters and fire a performance condition alert when the
value for the counter is less than, equal to, or greater than a defined threshold. The Create Alert Wizard can
be used to configure the SQL Server agent for alerts.
T-SQL (SS2K8) :: Check How Much Space Is Allocated To Database?
Dec 11, 2014how can i check how much space is allocated to the database in SQL Server
View 2 Replies View RelatedUpdate Cur_points With Cur_points+points, Then Wipe Points (was Help Needed Please)
Jun 22, 2007Greetings everyone, I'm posting this thread with the hope that someone will notice it and might offer me a helping hand regarding one of my problems.I have the database named "DBEXAMPLE" with the table MEMB_INFO that contains two important columns that are named cur_points and points where cur_points column contains the total available points that a member can use/spend on a game and the points column stores the total of the points that a member used so far.So as i plan to wipe all data of the database, i need to keep the member login,password and total points that each member purchased.So i will somehow need to update the cur_points column with the total of current cur_points+points columns and then wipe points column.I've personally asked a friend regarding this and he said that this should be something complicated and it might require php also.Really appreciate if someone could help me regarding this.small schema:Database: DBEXAMPLETable: MEMB_INFOColumn cur_points -> total available points that a member can spendColumn points -> total points that the member has already spentColumn memb___id -> member login aka account idTODO -> update cur_points column with the total of cur_points+points columns for each member(buyer)With best regards.
View 5 Replies View RelatedTransact SQL :: How To Retrieve Value From UDT
Jun 23, 2015I want to reach and display the value of @data inside if [wwtest].
The UDT is from the database [test] and I retrieve an error message "The type name 'test.dbo.tvf_id' contains more than the maximum number of prefixes. The maximum is 1.".
I don't know how to retrieve the data from the database test inside of the query document for [wwtest]?
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
[Code] .....
Transact SQL :: How To Retrieve All Tables From A View
Jun 29, 2015We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
View 6 Replies View RelatedTransact SQL :: Query To Retrieve Max Count
May 28, 2015The below is my query in SQL SERVER 2012
DECLARE
@PN_INC INT,
@POSITION_DESC VARCHAR(15)
SELECT @PN_INC= MAX(PositionNumberInc) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
SELECT @PN_INC is returning null when there is no record in the table;
1. how can I make it return 0 when @PN_INC is null else it should pick the MAX(PositionNumberInc) value
2. I have written the below query to return 0 when @PN_INC is null but I need query to select the MAX(PositionNumberInc) value when @PN_INC is not null
SELECT @PN_INC= coalesce(MAX(PositionNumberInc), 0) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
Transact SQL :: How To Retrieve Affected Rows
Aug 31, 2015I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
View 2 Replies View RelatedTransact SQL :: Remove Space In Value
Aug 3, 2015I'm getting data from a flat file and there is space before few values. I'm unable to remove that space through replace or LTRIM. How to remove these spaces.For E.g.
Values
' 2356'
' 1'
' 23'
' ABCD'
Transact SQL :: Retrieve Values Instead Of Joining To Same Table 5x
Jun 2, 2015I have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc. See U1 - U6
What is a better way to return this information rather than multiple joins?
Select
[AccountNumber]
,a.[AccountId]
,[Name]
,[new_LocationID]
,[ws_name]
,[new_BillingManageruserName]
,[new_AreaServiceManName]
[Code] ....
Transact SQL :: Retrieve Descendants With Depth Level
Nov 22, 2015I have a table of Affiliates, each Affiliate can have a ParentId, which refers to another Affiliate. How can I retrieve all children of a given Affiliate, along with their depth level? I have this query which selects the total number of descendants of a given Affiliate, but I need to select all the children + their depth level, up to a certain level.
WITH Descendants AS(
SELECT RootId = Id, Id
FROM Affiliates
UNION ALL
SELECT RootId, Af.Id
FROM Descendants
[Code] .........
What I want to achieve is a statement that has 3 parameters: RefererId, StartLevel, and EndLevel.
I need a statement that selects all the specified Affiliate.ReferrerId's descendants including their level distance from the referrer, filtered by the StartLevel and EndLevel (WHERE CurrentLevel >= StartLevel AND CurrentLevel <= EndLevel ).
Transact SQL :: String Split After The Space
May 18, 2015DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) -1, -1)) AS [FirstName],
RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1), LEN(@FullName))) AS [LastName]
This is only gives first and last not first and middle
DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT
STUFF(@FullName,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+5,LEN(@FullName),'') [Firstname1],
STUFF(@FullName,1,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+4,'') Lastname1
Not right as it gives
Vauxhall
Adam Rocks AIR Vauxhall
Ideally the result should be
Vauxhall
Adam Rocks AIR
Transact SQL :: Remove Space If Exists
Oct 23, 2015I am trying to bulk update about 50,000 rows in a SQL table. The values that the table MUST contain are:
1-3 days
4-7 days
8-10 days
Some of the rows contain a space between the number and the hyphen like:
1 - 3 days
4 - 7 days
8 - 10 days
What would be my best methodology of removing the space between numerics only? I have seen multiple examples of how to remove ALL whitespace, but I only want to remove the space between the numbers and the hyphen *IF* it exists. And the field type is varchar(200)
Transact SQL :: Retrieve Customers Invoiced Twice Or More In Previous Month
Oct 29, 2015I am trying to pick up the customers invoiced twice or more within a month. In the case below Pepsi Cola and Jack Daniel were invoiced twice in October. The query need to pickup the previous month, se being in October I need to pick up the invoice of September.
create table #forum (Customer varchar(20),Invoiced date)
insert into #forum values ('Pepsi Cola','2015-09-01') ,('Pepsi Cola','2015-09-06') ,
('Pepsi Cola','2015-10-01') ,('Pepsi Cola','2015-10-02') ,('Pepsi Cola','2015-11-01') ,
('Ferrarelle','2015-09-01') ,('Ferrarelle','2015-10-01') ,('Ferrarelle','2015-11-16') ,('Ferrarelle','2015-11-01') ,
('Jack Daniel','2015-09-01') ,('Jack Daniel','2015-09-04') ,('Jack Daniel','2015-09-06') ,('Jack Daniel','2015-09-30') ,
('Jack Daniel','2015-10-01') ,('Jack Daniel','2015-10-18') ,('Jack Daniel','2015-11-01') ,
('Bud','2015-09-01') ,('Bud','2015-10-01') ,('Bud','2015-11-01')
select * from #forum
Transact SQL :: Query To Retrieve To What Database A Table Belongs
Jul 16, 2015I need a query to list the tables in SQL sever with respective database they belong across databases.
I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?
The Sys.Databases View list the databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.
Which View will allow me to fetch database to table mappings?
Transact SQL :: Retrieve Last Class Attended By The Student Using JOIN
May 14, 2015In my query, I am showing Student Record and also want to retrieve the last class attended by the student using JOIN to the table (ClassHistory).
Master
ID Student_Name
1 Arindam
2 Brenda
3 Callie
(ClassHistory)
ID Class Year
1 4 2003
1 5 2004
1 6 2005
2 4 2003
2 5 2004
3 4 2003
DESIRED RESULTSET
ID Student_Name Class Record
1 Arindam 6
2 Brenda 5
3 Callie 4
The result is like - Arindam last class was Class 6, while Brenda was in Class 5 and Callie was in class 4.
Please note that the query will b part of a larger query and the Class record to be retrieved from ClassHistory if possible need to be retrieved through JOIN.
Transact SQL :: Returning Specific Characters After Space?
Jul 6, 2015I have a column that has data in column user4 such as :
CALBRIG ALBION COMPANY LTD
1900 No. 8 Road, RIchmond, BC, V6V 1W3
I can do a LEFT(user4, 7) That returns me the first 7 charcters but I need to retrieve the company information but the number of characters may vary with each field. Is there anyway I can do this without returning the entire line?
Transact SQL :: Table With SPARSE Column Need More Space?
Jul 24, 2015As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:
/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)
I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb
SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only 466,031MB !
That means the Table with SPARSE column need more storage, Why?
By the way, in table Sprstb column address has 1666198 Null mark (from 5000000)
Transact SQL :: Retrieve Another Column (exchange Rate) From Particular Date For Subquery?
May 25, 2015Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query.
Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.
how i can achieve this query without using RANK() and other functions.
"SELECT Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,
[Code] ....
Transact SQL :: Alter Table To Rename Column Name With No Space
Oct 27, 2015I want to alter my all tables to change the name of all columns.
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
Transact SQL :: Replace Special Characters And Space Except Dash
Sep 18, 2015I want to replace special characters and space except '-' Tried with Pathindex and function but not getting expected results.
Ex: Input kjkdjfdf-234#kei$ ewiw
output: kjkdjfdf-234keiewiw
Transact SQL :: Retrieve Return Result With Chronological Order Based On Parameter
Jun 23, 2015Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values
[Code] .....
Transact SQL :: Retrieve Latest Record Of Serial Number With Multiple Entries
Sep 30, 2015I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.
I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,
Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.
Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.
SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model
[Code] ....